SQL Server Date Functions: A Data Analysis Guide

Date functions are the bread and butter of data analysis and reporting with SQL Server. In this guide, I will show you practical examples of the use of SQL Server date functions and how to apply them to your reports.

A question for all data analysts: Do you want to be taken seriously? I’m sure you do. In that case, knowing SQL Server date functions is mandatory. Date and time data types are ubiquitous in databases, as they are essential for reporting. The only way to efficiently handle them in data analysis and reporting is by using date and time functions.

This guide is designed to up your data analysis game in SQL Server. I will show you the most useful date and time functions and how they can help you when preparing reports.

In the process, I will lean heavily on our Cookbook section. There, we give you practical recipes for approaching and solving different SQL problems. Some of the cookbooks discuss the problems in standard SQL. Others are focused on problems in a specific database, e.g., SQL Server, MySQL, PostgreSQL, Oracle, and SQLite. There are more than 180 cookbooks. I’ll link some of them when we discuss the practical use of different date and time functions. Feel free to bookmark our cookbooks and use them as a reference when you write your queries. That’s what they’re for!

Basic date and time functions are a foundational part of the SQL Server knowledge required in data analysis. These foundations also include logical operators, WHERE, JOINs, GROUP BY, subqueries, set operators, and knowing how to insert, update, and delete data from a database. To improve your reporting, you can add window functions, recursive queries, and GROUP BY extensions.

Is there something in the above list you’d like to learn? No need to look for learning resources. Our SQL from A to Z in MS SQL Server learning track, with seven courses and 850 exercises, is the most comprehensive way to get complete knowledge of all these SQL Server concepts.

Date and Time Data Types in SQL Server

SQL Server date functions are designed for working with date and time data types. Knowing at least the most common of these data types is a prerequisite. These include:

• date
• time
• datetime2
• smalldatetime

Data type: date

This is a data type for showing dates. Its default format is yyyy-MM-dd (year, month, and day).

Data type: time

This data type shows time: hours, minutes, seconds, and nanoseconds based on the 24-hour clock.  It uses the hh:mm:ss[.nnnnnnn] format.

Data type: datetime2

This data type shows both date and time. Its format is YYYY-MM-DD hh:mm:ss[.nnnnnnn], based on the 24-hour clock.

Data type: smalldatetime

This is used when you don’t require seconds in your date and time data. It has the format YYYY-MM-DD hh:mm:ss. The seconds are always zero, as they’re rounded up or down. If the seconds' value is 29.998 or less, the time is rounded down to the nearest minute. If it’s above, it’s rounded up to the nearest minute.

Now, let’s see how to use date and time functions to work with this data. As the following sections involve coding, ensure you have the SQL Server Cheat Sheet by your side for smooth sailing.

Comparing and Ordering Date/Time Values in SQL Server

It’s a common task for data analysts to find data before, after, or on a particular date. You can do that by comparing date and time values using WHERE and the comparison operators <, >, =, <=, and/or >=.

To order by date or time, use ORDER BY.

Example

Let’s work with the table user_registration. It shows the info about website users’ registration, last visit, and subscription dates. If you want to work with this table on your own, here’s the script.

As you can see, the columns registered_at and last_visit show the date and time. The columns subscription_start and subscription_end show only dates.

Problem: Select all the columns from the table. Show only users whose subscription ends after 2023. Sort the output descendingly by the subscription end date.

Solution:

SELECT *
FROM user_registration
WHERE subscription_end > '2023-12-31'
ORDER BY subscription_end DESC;

Explanation: Select all the columns from the table. Now, compare the column subscription_end with a date in WHERE. In this case, the ‘greater than’ operator compares it with 2023-12-31. This is to find all the subscriptions that end after 2023. The dates ‘greater than’ a given data are later than the given date.

Note that when specifying constant date/time values, you must put them in single quotes ('').

Then, use the same column in the ORDER BY clause to order the output descendingly. Later dates are ‘greater’, so we order from the latest to the most recent date here.

Output: There are three users that qualify.

Speaking of outputs, every data analyst should know how to export data from Microsoft SQL Server to a CSV file.

Recommended cookbooks to bookmark:

1. How to Compare Datetime Values in SQL
2. How to Compare Date Values in SQL
3. How to Order by Date in T-SQL

Extracting Parts of the Date

Sometimes, you don’t need the whole date. What if you want to show only days, months, or years? This is commonly done when you want to group data by day/month/year and to build reports with statistics for a given period.

SQL Server functions can help you with that task. These functions are DAY(), MONTH(), and YEAR().  They are named intuitively, so their use is clear.

• DAY() – Extracts the day from the date and shows it as an integer.
• MONTH() – Extracts the month from the date and shows it as an integer.
• YEAR() – Extracts the year from the date and shows it as an integer.

Their syntax is very straightforward. In these functions, you simply write the date (in single quotes) or refer to the column that has date values.

For example, the code SELECT DAY('2023-09-01') will return 1, the day value. The MONTH() and YEAR() functions work the same way, returning the month and year value respectively.

Alternatively, you could use the function DATEPART(). It works similarly: you also specify the date in parentheses. But there’s also an additional argument where you specify the date part you want to show, i.e., year, month, date.

Using the above example, to show only the day value, you would write: SELECT DATEPART(DAY, '2023-09-01').

Example

I’ll use the table from the previous example to show you how to extract parts of the date.

Problem: Count the number of subscriptions expiring each year.

Solution:

SELECT YEAR(subscription_end) AS subscription_expiry,
	 COUNT(user_id) AS number_of_subscriptions
FROM user_registration
GROUP BY YEAR(subscription_end); 

Explanation: I used the function YEAR() to extract the year part of the subscription_end values. I renamed the column subscription_expiry.

The second step is to use the COUNT() function and count the subscriptions. To get that number for each year, group the output by the year value. You need to use the GROUP BY clause together with the same calculation from the column subscription_expiry. Note: You can’t use a column alias in GROUP BY because SQL Server doesn’t allow it.

Output: There are two subscriptions ending in 2023 and three ending in 2024.

Recommended cookbooks to bookmark:

1. How to Get the Day from a Date in T-SQL
2. How to Get the Month from a Date in T-SQL
3. How to Get the Year from a Date in T-SQL
4. How to Group by Year in T-SQL
5. How to Group by Month in T-SQL

Formatting and Converting to Dates

Dates in databases are often saved in a format that’s too technical or unsuitable for reporting. There’s sometimes a need to reformat the dates and make your reports prettier and more readable.

The SQL Server functions used for that are:

• FORMAT() – Changes the date format.
• DATENAME() – Show the day’s name (i.e. Monday).

Also, dates may be saved as strings (text data), which is usually unsuitable for data analysis. In these scenarios, you can use three functions to convert strings to a desired data type.

• PARSE() – Converts a string containing date and time into a date/time data type.
• CAST() – Converts a string containing date and time into a date/time data type.
• CONVERT() – Converts a string containing date and time into a date/time data type.

Examples

I’ll show you a short example for each function, so you’ll also learn the syntax along the way.

FORMAT()

Problem: Let’s use FORMAT() to get the date from the registered_at values from our table and show it in the day/month/year format.

Solution:

SELECT user_id,
	 registered_at,
	 FORMAT(registered_at, 'dd/MM/yyyy') AS registered_formatted
FROM user_registration; 

Explanation: I select the user ID and the registration date and time, just for comparison. Then, I use the FORMAT() function. The first argument is the value I want to format, i.e. the column registered_at. The second argument is the desired format, which is specified in the single quotes. This format argument accepts a .Net framework format string, which must be either a standard format string or a custom pattern. The most common specifiers for a custom format are:

• d – The day of the month, from 1 to 31.
• dd – The day of the month, from 01 to 31. (Note the zero in front of the 1.)
• ddd – The abbreviated name of the day of the week (e.g. “Mon”).
• dddd – The full name of the day of the week.
• M – The month, from 1 to 12.
• MM – The month, from 01 to 12.
• MMM – The abbreviated name of the month (e.g. “Dec”).
• MMMM – The full name of the month.
• yyyy – The year as a four-digit number.
• hh – The hour, using a 12-hour clock from 01 to 12.
• H – The hour, using a 24-hour clock from 0 to 23.
• m – The minute, from 0 to 59.
• mm – The minute, from 00 to 59.
• s – The second, from 0 to 59.
• ss – The second, from 00 to 59.

Read more about the custom date and time format strings in the SQL Server documentation.

Output: The dates formatted this way are much easier to read, especially to an untrained, non-technical eye.

DATENAME()

I already talked about extracting the day part from the date using DATEPART() or DAY(). However, you’re not limited to that.

With the help of the DATENAME() function, you can also show the name of the day.  The only prerequisite is that you use weekday in the datepart argument of the function (the first argument).

Problem: Show the registration day name for each user.

Solution:

SELECT user_id,
	 registered_at,
	 DATENAME(weekday, registered_at) AS registration_day
FROM user_registration; 

Explanation: First, show the user_id and registered_at columns. Then, use DATENAME() to show the registration day names. The first argument in the column is weekday; this will show names instead of integers. The second argument is registered_at, as you want to extract day names from these column values.

Output: You’ve got your day names, which can be useful in analyzing which day of the week people register the most.

PARSE()

If I want to convert the string 'Wednesday, 20 December 2023' to the DATE data type, I could do that using the following code:

SELECT PARSE('Wednesday, 20 December 2023' AS DATE);

In the PARSE() function, I write a string (you can also reference a column with string values) and specify the data type I want to convert to.

The code will return the output 2023-12-20.

CAST()

The CAST() function is a standard SQL function for converting from one data type to another. It has the same syntax as PARSE().

Let’s use CAST() to get the same output as with PARSE(). For CAST() to work, the string must be in the T-SQL date and time data type format.

SELECT CAST('20 December 2023' AS DATE);

I write the string in single quotes and specify the output data type. The output is the same as in the earlier example: 2023-12-20.

CONVERT()

This is a SQL Server-specific function used for the same purpose as CAST().

Here’s how to convert a string to DATE using CONVERT():

SELECT CONVERT(DATE, '20 December 2023');

The first argument is the data type and the second is the value to be converted. Once again, the output is the same: 2023-12-20.

Recommended cookbooks to bookmark:

1. How to Convert Time Data from Strings in SQL Server
2. How to Change Date and Time Formats in T-SQL
3. How to Format a Date in T-SQL
4. How to Get Day Names in SQL Server

Date Arithmetic

In SQL Server, you’re not stuck with simply changing the date/time values cosmetically. You can also use the values from the database and perform arithmetic operations on them.

There are two SQL Server date functions for doing that:

• DATEADD() – Adds a number to a date interval and modifies the date/time.
• DATEDIFF() – Calculates the difference between two date/time values.

Example

Let’s again use our table user_registration.

Problem: Show the user IDs and the end dates of their subscriptions. Also, calculate how many days pass between users’ registrations and when they became subscribers. Finally, prolong each subscription by 30 days – this is a gift to our users – and show the new subscription end date.

Solution:

SELECT user_id,
	 subscription_end,
	 DATEDIFF (day, registered_at, subscription_start) AS subscription_lag,
	 DATEADD(day, 30, subscription_end) AS new_subscription_end
FROM user_registration;

Explanation: I use the DATEDIFF() function to calculate the difference between the registration and subscription dates. The function has three arguments. The first one specifies the interval (e.g. week, month, year) in which the difference will be expressed. In this case, I want the difference in days. The second argument is a start date, and the second is the end date.

Then, there’s the DATEADD() function for prolonging the subscription. This function, too, has three arguments. The first one is the interval (e.g., week, month, year) I want to add. I’m adding days. The second argument is for specifying the number of intervals I want to add. So these two arguments read as ‘add 30 days’. To what? To the values in the column subscription_end, which is a third argument.

Output: Here’s the output with the required calculations:

Recommended cookbooks to bookmark:

1. How to Add Days to a Date in T-SQL
2. How to Subtract 30 Days from a Date in T-SQL
3. How to Calculate the Difference Between Two Dates in T-SQL
4. How to Calculate the Difference Between Two Datetimes in T-SQL

Getting the Current Date and Time

In SQL Server, GETDATE() is the function for getting the current date and time. This is a T-SQL function, with the standard SQL version being CURRENT_TIMESTAMP.

Apart from its basic use, GETDATE() can also be used in conjunction with the other SQL Server date functions. Let’s see that in an example.

Example

Problem: Show each user ID and the date and time of the latest visit. Show the current date and time in a separate column. Additionally, show how many days the user has been inactive.

Solution:

SELECT user_id,
	 last_visit,
	 GETDATE() AS current_date_time,
	 DATEDIFF(DAY, last_visit, GETDATE()) AS days_of_inactivity
FROM user_registration; 

Explanation: I got the current date and time with the help of GETDATE(). The function has no arguments; just write its name and parentheses.

Now, the number of inactivity days is defined as the difference between the last visit and the current date. To calculate the difference between these values, use DATEDIFF(). You already know this function – the first argument defines the interval in which the difference will be shown. The second argument is the start date/time, and the third is the end date/time, i.e. the current date and time.

Output: Here’s the output with the days of inactivity:

Recommended cookbooks to bookmark:

1. How to Get the Current Date and Time (Without Time Zone) in T-SQL
2. How to Get the Current Date in SQL Server
3. How to Get the Current Date (Without Time) in T-SQL

Getting the End of the Month and Past Date Intervals

The SQL Server function EOMONTH() makes it easy to find the end of the month for any date. One of its uses is to turn all the dates into the last day of the month, i.e. when there’s a monthly reporting period.

You can do even more by combining other SQL Server date functions. For example, you can get the previous day, month, or year by using some of the familiar functions: DAY(), MONTH(), YEAR(), DATENAME(), DATEADD(), GETDATE(), CAST(), and CONVERT().

Example

The following examples will show how to use EOMONTH() and how to find the previous year.

Problem: Show users’ IDs and when their subscription started. Additionally, turn all the subscription dates into the last day of the month for a reporting period. Also, show the previous reporting year based on the reporting period.

Solution:

SELECT user_id,
	 subscription_start,
	 EOMONTH(subscription_start) AS reporting_period,
	 YEAR(DATEADD(YEAR, -1, EOMONTH(subscription_start))) AS previous_reporting_year
FROM user_registration;

Explanation: The EOMONTH() function’s syntax is very easy: write the name of the function and pass the date value. In our case, it’s the column subscription_start.

Now, to find the previous year, you need several functions. Let’s use YEAR() to extract the year from the date value. We define the date value in the DATEADD() function. There, we want to add one year to the reporting period. However, as we want to get the previous year, we add -1 year – i.e. we’re subtracting.

Output: Here’s the output with the reporting periods:

Recommended cookbooks to bookmark:

1. How to Get Yesterday’s Date in T-SQL
2. How to Get the Last Day of the Month in T-SQL
3. How to Get the Previous Month in T-SQL

SQL Server Date Functions: The Making of a Successful Data Analyst

The examples speak for themselves! I trust there’s no need to convince you further how essential SQL Server date functions are in a data analyst’s job. They allow you to manipulate dates and times in numerous ways. This will add to your flexibility and versatility – and your reports will become more readable and informative.

However, leveraging date functions to their full potential implies familiarity with many other SQL Server concepts. Not sure what they are? You’ll find them among these SQL Server interview questions. If you are uncomfortable with some of them, our SQL from A to Z in MS SQL Server learning track will help you stay on course.

When it comes to improving your data analysis skills with SQL Server date functions, any time is the right time!