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.
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.
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.
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:
1 2 3 4 5 6 7 8
SELECT ISDATE('2019-08-24') AS A, ISDATE('2019-08-24 13:35') AS B, ISDATE('2019-08-24 13:35:12') AS C, ISDATE('12:35') AS D, ISDATE('2019-08-32') AS E, ISDATE('12:61') AS F, 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.
To view the current date and time, you can use
CURRENT_TIMESTAMP with the same results as shown in Fig. 2:
SELECT GETDATE() AS [Using GETDATE()] SELECT 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.
Several functions are available to extract date and time parts from valid date/time expressions:
1 2 3 4 5 6 7
SELECT DATENAME(MONTH, '2019-08-24') AS [Month using DATENAME], DATENAME(YEAR, '2019-08-24') AS [Year using DATENAME], DATENAME(DAY, '2019-08-24') AS [Day using DATENAME], DATENAME(WEEKDAY, '2019-08-24') AS [Day of week using DATENAME], DATENAME(DAYOFYEAR, '2019-08-24') AS [Day of year using DATENAME], DATENAME(QUARTER, '2019-08-24') AS [Quarter using DATENAME]
DATEPART()might be more appropriate:
1 2 3 4 5 6 7
SELECT DATEPART(MONTH, '2019-08-24') AS [Month using DATEPART], DATEPART(YEAR, '2019-08-24') AS [Year using DATEPART], DATEPART(DAY, '2019-08-24') AS [Day using DATEPART], DATEPART(WEEKDAY, '2019-08-24') AS [Day of week using DATEPART], DATEPART(DAYOFYEAR, '2019-08-24') AS [Day of year using DATEPART], DATEPART(QUARTER, '2019-08-24') AS [Quarter using DATEPART]
Fig. 3 shows the output of the two previous query sets:
The arguments to
DATEPART()are a date or time part and the date/time expression, respectively.
In addition to
DATEPART(), you can alternatively use
YEAR() to get the integer representation of the day, month, and year of a given date that is passed as parameter.
To form dates and times using individual parts (milliseconds, seconds, minutes, hours, days, months, and years), the
DATETIMEFROMPARTS() functions can be utilized as illustrated in Fig. 4:
1 2 3 4 5
SELECT DATEFROMPARTS(1989, 12, 2) AS FirstDate, DATEFROMPARTS(2003, 11, 25) AS SecondDate, DATETIMEFROMPARTS(2019, 8, 24, 19, 23, 12, 0) AS ThirdDate, 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.
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.
1 2 3 4
SELECT DATEADD(DAY, 30, '2019-02-14') AS InvoiceIsDue, DATEADD(WEEK, -1, GETDATE()) AS OneWeekAgo, 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:
1 2 3 4
SELECT EOMONTH(GETDATE()) AS LastDayCurrentMonth, EOMONTH(GETDATE(), -1) AS LastDayLastMonth, EOMONTH(GETDATE(), 1) AS LastDayNextMonth
Fig. 6 shows the output of the above queries. As you can see,
EOMONTH() always returns a date.
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:
1 2 3 4 5
SELECT DATEDIFF(DAY, GETDATE(), EOMONTH(GETDATE())) AS H, DATEDIFF(DAY, GETDATE(), DATEADD(MINUTE, 3000, GETDATE())) AS I, DATEDIFF(HOUR, '2019-01-01 00:00:00', GETDATE()) AS J, 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.
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.
Using the Orders, Customers, and Employees tables, let us explore how to retrieve the following information:
1 2 3 4
SELECT COUNT(OrderId) AS ShippedOnTime FROM Orders WHERE DATEDIFF(DAY, OrderDate, ShippedDate) <= 2;
1 2 3 4 5
SELECT CONCAT(FirstName, ' ', LastName) AS Employee, HireDate FROM Employees WHERE HireDate < DATEADD(YEAR, -26, EOMONTH(GETDATE()))
1 2 3 4 5 6
SELECT DATENAME(MONTH, OrderDate) AS [Month], COUNT(OrderId) AS [Order count by month] FROM Orders GROUP BY DATENAME(MONTH, OrderDate) ORDER 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.
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.