Using Date and Time Data Types and Functions in SQL Server
Introduction
All modern applications manipulate date and time information, in one way or another. From a hotel reservation system to a manufacturing execution system and all the way to complex data warehouses, software developers need to know how to handle dates and times effectively. Without exception, they will prefer to focus on the business requirements instead of dealing with data on the application side. They can accomplish that goal through functions provided by the database system, which leads to code that is easier to read and more maintainable.
In this guide, we will employ the Northwind database to illustrate the use of date and time functions in SQL Server 2014 and newer. You can download the installation script from the Microsoft SQL Server Samples repository and use it under the terms of the MIT license.
Except where explicitly noted, all the functions used in this guide are specific to T-SQL (Transact SQL), Microsoft's implementation of the Structured Query Language.
Date and Time Data Types
The most popular date and time data types in SQL server are:
time represents the time of day using a 24-hour clock with a resolution of 100 nanoseconds (hh:mm:ss.nnnnnnn) but without timezone information.
date is self-explanatory. By default, it follows the expanded ISO8601 standard (yyyy-mm-dd) but also accepts other variations (such as dd/mm/yy).
smalldatetime combines date and time, but with a precision of one minute. The seconds in hh:mm:ss are rounded up or down to the nearest minute.
- datetime is similar to smalldatetime, but with a resolution to the milliseconds.
The choice of one data type over the other is mostly a matter of the required precision and the wise usage of the available storage space. While time requires three to five bytes, date needs three, whereas smalldatetime and datetime demand four and eight, respectively. If neither precision or space are concerns, datetime is most likely the best solution and often represents the preferred choice.
Date and Time Functions
The most common operations with date and time data fall into one of the following categories:
Determine if an expression is a valid date or time.
View current date and time.
Given a date and time, return the year, month, day, or weekday in the form of a string or an integer (e.g. October or 10 represent the same month).
Build times and/or dates using individual hour, minute, second, day, month, and year parts.
Add (or subtract) date/time parts from a given date.
- Calculate the difference between two dates or times.
Let us discuss and illustrate the above in more detail through examples.
Example 0: Expression Is a Valid Date or Time
Before further processing takes place on an expression that is supposed to be a date or time, the ISDATE() function can be used to determine its validity. ISDATE() returns 1 if the expression is a valid date/time or 0 otherwise, as you can see in Fig. 1:
1SELECT
2 ISDATE('2019-08-24') AS A,
3 ISDATE('2019-08-24 13:35') AS B,
4 ISDATE('2019-08-24 13:35:12') AS C,
5 ISDATE('12:35') AS D,
6 ISDATE('2019-08-32') AS E,
7 ISDATE('12:61') AS F,
8 ISDATE('25:12') AS G
In this instance, A through D are 1 because:
2019-08-24 is a valid date.
- 2019-08-24 13:35 is a valid date with hour and minute data. The same applies to 2019-08-24 13:35:12, which also includes seconds.
On the other hand, E through G are 0 because:
August does not have 32 days, thus making 2019-08-32 an invalid date.
- Days have 24 hours and each hour has 60 minutes, which makes 25:12 and 12:61 invalid times.
In addition to character strings, ISDATE() admits, as an argument, other date and time functions, as we will explain later.
Example 1: Viewing the Current Date and Time
To view the current date and time, you can use GETDATE() or CURRENT_TIMESTAMP with the same results as shown in Fig. 2:
1SELECT GETDATE() AS [Using GETDATE()]
2SELECT CURRENT_TIMESTAMP AS [Using CURRENT_TIMESTAMP]
It is worth noting that GETDATE() is specific to SQL Server, whereas CURRENT_TIMESTAMP is part of the ANSI SQL standard and, thus, is portable across other database systems.
Example 2: Returning Date and Time Parts
Several functions are available to extract date and time parts from valid date/time expressions:
- If you are interested in converting months and days of the week to their corresponding names, use
DATENAME()as follows:
1SELECT
2 DATENAME(MONTH, '2019-08-24') AS [Month using DATENAME],
3 DATENAME(YEAR, '2019-08-24') AS [Year using DATENAME],
4 DATENAME(DAY, '2019-08-24') AS [Day using DATENAME],
5 DATENAME(WEEKDAY, '2019-08-24') AS [Day of week using DATENAME],
6 DATENAME(DAYOFYEAR, '2019-08-24') AS [Day of year using DATENAME],
7 DATENAME(QUARTER, '2019-08-24') AS [Quarter using DATENAME]- In case that the integer equivalents are more suitable for your needs,
DATEPART()might be more appropriate:
1SELECT
2 DATEPART(MONTH, '2019-08-24') AS [Month using DATEPART],
3 DATEPART(YEAR, '2019-08-24') AS [Year using DATEPART],
4 DATEPART(DAY, '2019-08-24') AS [Day using DATEPART],
5 DATEPART(WEEKDAY, '2019-08-24') AS [Day of week using DATEPART],
6 DATEPART(DAYOFYEAR, '2019-08-24') AS [Day of year using DATEPART],
7 DATEPART(QUARTER, '2019-08-24') AS [Quarter using DATEPART]Fig. 3 shows the output of the two previous query sets:
The arguments to
DATENAME()andDATEPART()are a date or time part and the date/time expression, respectively.
In addition to DATENAME() and DATEPART(), you can alternatively use DAY(), MONTH(), or YEAR() to get the integer representation of the day, month, and year of a given date that is passed as parameter.
Example 3: Building Times and Dates from Individual Parts
To form dates and times using individual parts (milliseconds, seconds, minutes, hours, days, months, and years), the DATEFROMPARTS() and DATETIMEFROMPARTS() functions can be utilized as illustrated in Fig. 4:
1SELECT
2 DATEFROMPARTS(1989, 12, 2) AS FirstDate,
3 DATEFROMPARTS(2003, 11, 25) AS SecondDate,
4 DATETIMEFROMPARTS(2019, 8, 24, 19, 23, 12, 0) AS ThirdDate,
5 DATETIMEFROMPARTS(2019, 8, 25, 0, 12, 32, 123) AS FourthDate
The above image shows that in both functions, the first argument is the year, the second is the month, the third is the da,y and so on. While DATEFROMPARTS() requires only those three, DATETIMEFROMPARTS() also needs the millisecond, second, minute, and hour data.
Example 4: Adding or Subtracting Date/Time Parts from Dates
Given a date or time, it may be necessary to add or subtract a number of minutes, hours, or days, to name a few examples. To accomplish this goal, DATEADD() can come in handy to address several cases as seen in Fig. 5.
1SELECT
2 DATEADD(DAY, 30, '2019-02-14') AS InvoiceIsDue,
3 DATEADD(WEEK, -1, GETDATE()) AS OneWeekAgo,
4 DATEADD(MINUTE, 45, GETDATE()) AS FortyFiveMinutesFromNow
As demonstrated in this example, common scenarios like
When is the invoice due if the delivery date was on Valentine's Day?
What was the exact date one week ago?
- What time will it be in 45 minutes from now?
can easily be handled using DATEADD() where the first parameter is the date or time part, the second is the number to add or subtract, and the last is a date/time expression. In any event, the function always returns a datetime value.
At times, it may be of interest to return the last day of the current, previous, or next months. To do this, you can employ EOMONTH() with a date and an optional number of months to add or subtract from that date:
1SELECT
2 EOMONTH(GETDATE()) AS LastDayCurrentMonth,
3 EOMONTH(GETDATE(), -1) AS LastDayLastMonth,
4 EOMONTH(GETDATE(), 1) AS LastDayNextMonth
Fig. 6 shows the output of the above queries. As you can see, EOMONTH() always returns a date.
Example 5: Calculating Differences between Dates or Times
If you need to find out how many days have elapsed since a given date, T-SQL provides DATEDIFF(). Of course, this function can also return other date or time parts, not just days.
Let us build on top of some of the functions that we have discussed so far. Read the following queries and look at Fig. 7 to understand what they do before proceeding to the explanation below:
1SELECT
2 DATEDIFF(DAY, GETDATE(), EOMONTH(GETDATE())) AS H,
3 DATEDIFF(DAY, GETDATE(), DATEADD(MINUTE, 3000, GETDATE())) AS I,
4 DATEDIFF(HOUR, '2019-01-01 00:00:00', GETDATE()) AS J,
5 DATEDIFF(SECOND, '2019-08-02 07:30:00', '2019-08-10 19:30:00') AS K
H tells us how many days until the end of the current month.
I represents the number of days between right now and now + 3000 minutes.
J shows the total hours that have elapsed since the beginning of 2019.
- K is the number of seconds between August 2 07:30:00 am and August 10 07:30:00 pm.
As with other functions, the order of the parameters passed to
DATEDIFF()matters. The first parameter is the date or time part, whereas the second and the third are the start and end date/time values.
Remember that your results will be different based on when you execute these examples - unless you have a time machine and can return to the exact date when I was writing this guide.
Putting It All Together: Using the Northwind Database
Using the Orders, Customers, and Employees tables, let us explore how to retrieve the following information:
- How many orders were shipped within two days?
1SELECT
2 COUNT(OrderId) AS ShippedOnTime
3FROM Orders
4WHERE DATEDIFF(DAY, OrderDate, ShippedDate) <= 2;- Which employees were hired more than 26 years ago as of the last day of the current month?
1SELECT
2 CONCAT(FirstName, ' ', LastName) AS Employee,
3 HireDate
4FROM Employees
5WHERE HireDate < DATEADD(YEAR, -26, EOMONTH(GETDATE()))- Historically, what are the months with the greatest and lowest number of orders? As you can see in Fig. 8, April appears to be the best month of the year, whereas June shows a drastic decrease in sales:
1SELECT
2 DATENAME(MONTH, OrderDate) AS [Month],
3 COUNT(OrderId) AS [Order count by month]
4FROM Orders
5GROUP BY DATENAME(MONTH, OrderDate)
6ORDER BY Count(OrderId) DESC
These examples (and countless others that you can think of) highlight the importance of using date and time functions in SQL Server to facilitate development and drive decision-making.
Summary
In this guide, we have explained what the most popular date and time data types and functions in SQL Server are. Additionally, we have demonstrated how to leverage them to manipulate information on the database end. This can help programmers focus on development instead of worrying about how to properly format and present that information on the application side.