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

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:

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:

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:

Equal To (=)

Comparing for equality is straightforward using the operator.

This query will return messages sent exactly at August 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:

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.

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".

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:

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.

Facebook/META SQL Interview Question Using Dates

Let's solve a real SQL Interview question asked by Meta/Facebook, where your given a a table of Facebook posts:

Example Input:

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:

What's Next: ADVANCED SQL

Congrats, you've learned pretty much every major concept needed to build a foundation in SQL, and ace the SQL interview. Now it's time to go from good to great at SQL in the advanced SQL tutorial!

Advanced SQL Tutorial