# SQL Date-Time Functions Tutorial With Practice Exercises

In past tutorials, we've worked with text and numeric data, but conveniently ignored date & timestamp data. In this SQL lesson, we'll cover how to use:

• , and to return current date, time and timestamp.
• Comparison operators , , , , and to compare dates
• and to extract specific components of date.
• to round down date or timestamp into specific level of precision.
• to add or subtract time intervals in calculations.
• to convert date or timestamp into strings.
• , , , and to convert strings into date or timestamp.

#### Date and Time Functions Example: Handling Date and Time with Microsoft Team's Messages

In this tutorial, we'll use the dataset from our Microsoft SQL Interview Question which contains messages sent on Microsoft Teams.

Table: Sample Data

10025206987Send this out now!08/16/2021 00:00:00
92236014500Get on the call08/10/2022 00:00:00
81923104500What's the status on this?07/10/2022 00:00:00
74336018752Let's take this offline06/14/2022 00:30:00
...............
99025208520Maybe it was done by the automation process.08/19/2022 00:00:00

#### Getting the Current Date and Time in SQL

If you want to find out the current date and time, here's the functions that you can use:

• : Returns today's date.
• : Returns the current time without the date.
• (or ): Returns the current date and time.

Let's apply these functions onto the table:

Bear in mind, the dates and times you see in the results are based on when you run this query. In this case, it's on 08/27/2023. This is why the dates might not match the values from the table β they're showing the current date and time!

Here's the first 3 rows of the results:

message_idsent_datecurrent_datecurrent_timecurrent_timestamp
10008/16/2021 00:00:0008/27/2023 00:00:0007:35:15.989933+0008/27/2023 07:35:15
92208/10/2022 00:00:0008/27/2023 00:00:0007:35:15.989933+0008/27/2023 07:35:15
81907/10/2022 00:00:0008/27/2023 00:00:0007:35:15.989933+0008/27/2023 07:35:15

#### Comparing Dates Using Comparison Operators in SQL

Greater Than (>) and Less Than (<)

To retrieve records where a date is greater than or less than another date, you can use the > and < operators respectively.

For example:

This query will return messages sent after August 10, 2022:

88836017855DataLemur has awesome user base!08/12/2022 08:45:00
96636017852Meet me in five!08/17/2022 02:20:00
94225203561How much do you know about Data Science?08/17/2022 13:44:00

Greater Than or Equal To (>=) and Less Than or Equal To (<=)

Sometimes, you might need to include the boundary dates in your comparison. For this purpose, you can use the and operators.

This query will return messages sent on or after August 10, 2022:

90136014500You up?08/03/2022 16:43:00
88836017855DataLemur has awesome user base!08/12/2022 08:45:00
92236014500Get on the call08/10/2022 17:03:00
94225203561How much do you know about Data Science?08/17/2022 13:44:00

Equal To (=)

Comparing for equality is straightforward using the operator.

This query will return messages sent exactly at August 10, 2022, 17:03:00:

92236014500Get on the call08/10/2022 17:03:00

#### Extracting Parts from Dates in SQL

This is pretty straightforward! π The function extracts a specific component (i.e. year, month, day, hour, or minute) from a date or timestamp. You can also use for the same purpose.

You'll see that both functions give the same results:

message_idsent_dateextracted_yearpart_yearextracted_monthpart_monthextracted_daypart_dayextracted_hourpart_hourextracted_minutepart_minute
90108/03/2022 16:43:0020222022883316164343
74306/14/2022 14:30:002022202266141414143030
88808/12/2022 08:45:0020222022881212884545

With this handy tool, you'll be able to perform fun analysis like analyzing monthly sales trend, hourly website traffic, or daily customer interactions, and calculating averages, sums, or counts!

#### Truncating Date and Time in SQL

The rounds down a date or timestamp to a specified unit of time. In other words, it trims the finer details and retains the specified unit.

Here's what's happening in the results:

• : It rounds down the date to the beginning of the month. For example, if a message was sent on August 3rd, 2022 at 16:43, it's snapped to August 1st, 2022, while retaining the year and month.
• : It rounds down the date to the beginning of the day. The same August 3rd message becomes August 3rd, 2022, with the hour, minute, and seconds set to zero.
• : It rounds down the time to the beginning of the hour. The message becomes August 3rd, 2022 at 16:00, with the minutes and seconds set to zero.
message_idsent_datetruncated_to_monthtruncated_to_daytruncated_to_hour
90108/03/2022 16:43:0008/01/2022 00:00:0008/03/2022 00:00:0008/03/2022 16:00:00
74306/14/2022 14:30:0006/01/2022 00:00:0006/14/2022 00:00:0006/14/2022 14:00:00
88808/12/2022 08:45:0008/01/2022 00:00:0008/12/2022 00:00:0008/12/2022 08:00:00

#### Adding and Subtracting Intervals in SQL

The function is used to handle date and time gaps by adding and subtracting intervals such as "3 days", "5 hours", "45 minutes".

90108/03/2022 16:43:0008/05/2022 16:43:0007/31/2022 16:43:0008/03/2022 18:43:0008/03/2022 16:33:00
74306/14/2022 14:30:0006/16/2022 14:30:0006/11/2022 14:30:0006/14/2022 16:30:0006/14/2022 14:20:00
88808/12/2022 08:45:0008/14/2022 08:45:0008/09/2022 08:45:0008/12/2022 10:45:0008/12/2022 08:35:00

#### Formatting Dates in SQL

converts a date or timestamp to a string with a specified format.

Here's some commonly-used datetime formats for analysis:

Format NameFormatExample
ISO 8601 Date and Time'2023-08-27 14:30:00'
Date and Time with 12-hour Format'2023-08-27 02:30:00 PM'
Long Month Name, Day and Year'August 27th, 2023'
Short Month Name, Day and Year'Aug 27, 2023'
Day, Month, and Year'27 August 2023'
Day of the Month'August'
Day of the Week'Saturday'

message_idsent_dateformatted_iso8601formatted_12hrformatted_longmonthformatted_shortmonthformatted_daymonthyearformatted_dayofmonthformatted_dayofweek
90108/03/2022 16:43:002022-08-03 04:43:002022-08-03 04:43:00 PMAugust 03rd, 2022Aug 03, 202203 August 2022AugustWednesday
74306/14/2022 14:30:002022-06-14 02:30:002022-06-14 02:30:00 PMJune 14th, 2022Jun 14, 202214 June 2022JuneTuesday
88808/12/2022 08:45:002022-08-12 08:45:002022-08-12 08:45:00 AMAugust 12th, 2022Aug 12, 202212 August 2022AugustFriday

#### Casting Strings into Date and Timestamp in SQL

Remember in the SQL division tutorial we talked about the concept of casting, to turn decimals into integers and vice-versa?

You can use casting for date and timestamp data too! If you encounter date or timestamp data that's formatted as strings, here's two SQL functions that can help:

• or : Convert strings into dates.
• or : Convert strings into timestamps.

sent_datecasted_dateconverted_to_datecasted_timestampconverted_to_timestamp
08/03/2022 16:43:0008/03/2022 00:00:0008/27/2023 00:00:0008/03/2022 16:43:0008/27/2023 10:30:00
06/14/2022 14:30:0006/14/2022 00:00:0008/27/2023 00:00:0006/14/2022 14:30:0008/27/2023 10:30:00
08/12/2022 08:45:0008/12/2022 00:00:0008/27/2023 00:00:0008/12/2022 08:45:0008/27/2023 10:30:00

#### Facebook/META SQL Interview Question Using Dates

Example Input:

user_idpost_idpost_datepost_content
15165259941507/10/2021 12:00:00Need a hug
66109362435607/29/2021 13:00:00Bed. Class 8-12. Work 12-3. Gym 3-5 or 6. Then class 6-10. Another day that's gonna fly by. I miss my girlfriend
00423978425407/04/2021 11:00:00Happy 4th of July!
66109344256007/08/2021 14:00:00Just going to cry myself to sleep after watching Marley and Me.
15165211176607/12/2021 19:00:00I'm so done with covid - need travelling ASAP!

Write a query to find the number of days between each userβs first post of the year and last post of the year in the year 2021. Output the user and number of the days between each user's first and last post.

Hint: You can use the aggregate functions MIN/MAX on dates too! For example, here's a SQL query that finds the most recent date a user posted: