When you build applications that store data in SQL Server you will most likely have to store dates and times, and you'll call functions to do date manipulations. It is important to understand the different date and time data types, and when to use one data type over another. In this level I will be exploring the different date and time data types and discussing when each type is appropriate. Additionally I will be reviewing the SQL Server functions that can be used to compare, modify, derive and validate date and time values.
Which Date/Time Data Type Should I Use?
When Microsoft introduced SQL Server 2008 they provided some new date and time data types. With all the different date/ time data types and precision variations for some of date/time data types, you need make sure you pick the correct data type and precision based on the date and time requirements of your application. How do you decide which data/time data type to use?
Picking the right data/time data type will depend on your requirements. Does your application only require a date? Or your application requires only storing the time? Possibly your application needs to store both a date and a time value. If this is the case then how much time precision might your application need to store? Does your time portion only need to be accurate to the minute, the second, or some fraction of second precision? Once you know what your requirements are you can pick the right date/time data type.
Keep in mind that all date/time data types are not created equal. Some allow you to store dates, while others allow you to store time. And then there are some that allow you to store both date and time. Each date and time data type requires a specific number of bytes of disk space to store the date, time, or date/time value. When considering a specific date/time data type you need to make sure it will store the date/time values required by your application, without taking more space than necessary.
Table 1 lists the specifications for each date and time data type.
| Data type | Format | Range | Accuracy | Storage size (bytes) | User-defined fractional second precision | Time zone offset |
| time | hh:mm:ss[.nnnnnnn] | 00:00:00.0000000 through 23:59:59.9999999 | 100 nanoseconds | 3 to 5 | Yes | No |
| date | YYYY-MM-DD | 0001-01-01 through 9999-12-31 | 1 day | 3 | No | No |
| smalldatetime | YYYY-MM-DD hh:mm:ss | 1900-01-01 through 2079-06-06 | 1 minute | 4 | No | No |
| datetime | YYYY-MM-DD hh:mm:ss[.nnn] | 1753-01-01 through 9999-12-31 | 0.00333 second | 8 | No | No |
| datetime2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 | 100 nanoseconds | 6 to 8 | Yes | No |
| datetimeoffset | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) | 100 nanoseconds | 8 to 10 | Yes | Yes |
Table 1: Date and Time data types.
To better understand to the importance of picking the right data type for your date/time data, let me present a couple of examples.
For the first example, let's assume my application only needs to store a date value. In this case I have the option to use any of the following date/time data types since all these allow me to store a date: date, smalldatetime, datetime, datetime2, and datetype2offset. But only the date data typesupports only storing the date, the others allow me to store time, and one allows me to store the time zone offset with the date and time. The date data type only takes 3 bytes of space, whereas the other data types mentioned support storing a date value but also store additional information and therefore take more storage space.
For my next example assume I have to store a date and time value, where the seconds portion of the time needs to be accurate to 3 decimal places. In this case there are three date/time data types that meet this criteria: datetime, datetime2, and datetimeoffset. Which one might should I pick? If space is important then I should pick the datetime2 date type with a precision of 3. The reason this is best is because a datetime2 column with a precision of 3 takes up 7 bytes, whereas a datetime column takes up 8 bytes. I can show the different storage requirements by running the code in Listing 1.
-- Comparing Space Usage between DATETIME and DATETIME2
CREATE TABLE DATETIME_Test (DATETIME_TYPE DATETIME)
CREATE TABLE DATETIME2_Test(DATETIME2_TYPE DATETIME2(3));
GO
SET NOCOUNT ON;
INSERT INTO DATETIME_Test VALUES ('2015-10-30 09:51:00.123');
INSERT INTO DATETIME2_Test VALUES ('2015-10-30 09:51:00.123');
GO 100000
EXEC sp_spaceused @objname = 'DATETIME_Test';
EXEC sp_spaceused @objname = 'DATETIME2_Test';
GO
DROP TABLE DATETIME_Test, DATETIME2_Test;Listing 1: Comparing Space Usage between DATETIME, and DATETIME2
When I run the code in Listing 1 I get the output in Result 1 (output reformatted for readability):
Beginning execution loop Batch execution completed 100000 times. name rows reserved data index_size unused --------------- ------ -------- ------- ---------- ---------------- DATETIME_Test 100000 1824 KB 1768 KB 32 KB 24 KB name rows reserved data index_size unused --------------- ------ -------- ------- ---------- ---------------- DATETIME2_Test 100000 1696 KB 1664 KB 32 KB 0 KB
Result 1: Results when running code in Listing 1
By reviewing the “data” column value in Result 1 you can see that when I used a datetime2 column I achieved a 6% space saving when I stored 100,000 rows.
When picking a date/time data types you need to make a decision as to which is the right data type for your situation.
Function Parameters
Most of the date and time functions I'll be discussing in this article require one or more parameters. Rather than repeat the definition of those common parameters multiple times, I will be identifying the parameters and their definitions once in Table 2. I will use these parameters for the examples in this article.
| Parameter | Definition |
| datepart | The date interval for date comparison. The datepart value needs to be one of these: year, yy, yyyy, quarter, qq, q, dayofyear, dy, y, day, dd, d, week, wk, ww, hour, hh, minute, mi, n, millisecond, ms, microsecond, mcs, nanosecond, ns. For an explanation of each of these refer to Books Online. |
| startdate | A datetime expression that represents the first date of the date compare. |
| enddate | A datetime expression that represents the second date of the date compare. |
| date | An expression that evaluates to a datetime2 data type. |
| number | An integer expression that represents the number of intervals. |
| datetimeoffset | An expression that represents a datetime offset value. |
Table 2: Parameters for the date/time functions
As I show you examples below you can refer to Table 2 to find the descriptions for any of the parameters.
Functions that Compare, Modify and Derive Date/Time values
If you have to compare two dates, or derive a date with TSQL code, then SQL Server has a number of different functions that can meet your needs. In this section I will be going through those functions.
The first function I will be looking at is the DATEDIFF function. If you need to calculate the number of time intervals between two column values then you could use this function to meet that requirement. The DATEDIFF functions returns a signed integer value. Here is the syntax for the DATEDIFF function: DATEDIFF(<datepart>, <startdate>, <enddate>).
There are some limitation as to how far apart two date parameters can be when using the millisecond, or second datepart parameter. For milliseconds the maximum difference is 24 days, 20 hours, 31 minutes, and 23.647 seconds. For seconds the maximum difference is 68 years.
The code in Listing 2 shows you different ways to compare two dates using the DATEDIFF function.
DECLARE @BeginDate datetime = '1980-09-01 00:00';
SELECT DATEDIFF(MI,@BeginDate,'1980-09-01 00:47') AS 'Mi'
,DATEDIFF(DAY,@BeginDate,'1980-09-02 02:00') AS 'Day'
,DATEDIFF(HOUR,@BeginDate,'1980-09-02 02:00') AS 'Hour'
,DATEDIFF(Week,@BeginDate,'1980-09-08 00:00') AS 'Week'
,DATEDIFF(Year,@BeginDate,'1981-09-01 00:00') AS 'Year';Listing 2: Execute DATEDIFF function with parameter values
When I run the code in Listing 2 I get the results in Result 2.
Mi Day Hour Week Year ----------- ----------- ----------- ----------- ----------- 47 1 26 1 1
Result 2: Results when Listing 2 is executed
In Listing 2 I executed 5 different DATEDIFF function calls. For each call I used a different DATEDIFF datepart parameter values, the same startdate parameter values, and different enddate parameters. Each enddate parameter value was specifically set to help show the results of the different date comparison intervals. If you review the output of each function call in Result 2 you can see it is quite easy to identify the number of date intervals between two date values based on a particular date part, like minute, day, week, etc. Let me review this further by looking at the first function call:
DATEDIFF(MI,@BeginDate,'1980-09-01 00:47') AS Mi
In this DATEDIFF function call, I tellsSQL Server to compare the number of minutes between the two different date/time parameters. In this example I want to compute the number of minutes between ‘1980-09-01 00:00:00' and '1980-09-01 00:47'. For this example the DATEDIFF function call returns the signed integer value 47, as can be seen by reviewing the output in Results 2. I leave it up to you to review the other DATEDIFF function calls in this example.
You may be asking yourself “What boundaries are used to determine the difference between two different datetime values?” The DATEDIFF function returns the number of intervals between the startdate and enddate, where an interval is determined by the datepart parameter. If the difference between two date/time values fall into the same interval then the DATEDIFF function returns a 0. To better understand this lets review Listing 3 that has a number of examples where the startdate parameter and enddate parameter are different by .0000001 seconds.
DECLARE @BeginDate datetime2 = '1980-12-31 23:59:59.999999',
@EndDate datetime2 = '1981-01-01 00:00:00.9999999';
SELECT DATEDIFF(MI,@BeginDate, @EndDate) AS 'Mi'
,DATEDIFF(DAY,@BeginDate, @EndDate) AS 'Day'
,DATEDIFF(HOUR,@BeginDate, @EndDate) AS 'Hour'
,DATEDIFF(Week,@BeginDate, @EndDate) AS 'Week'
,DATEDIFF(Year,@BeginDate, @EndDate) AS 'Year';Listing 3: Example to show boundary difference with DATEDIFF
When I run the code in Listing 3 I get the results in Result 16.
Mi Day Hour Week Year ----------- ----------- ----------- ----------- ----------- 1 1 1 0 1
Result 3: Results when Listing 3 is executed
In the code in Listing 3 I declare two datetime2 variables @BeginDate and @EndDate. I set the @BeginDate to the very last millisecond of the year 1980, and then I set the @EndDate to the very first millisecond of the year 1981. I then used these two dates to run through 5 different DATEDIFF function calls. As you can see by reviewing the output in Results 3 all function calls but 1 returned a 1. The one exception was when I called the DATEDIFF function with a datepart parameter set to 'Week', which returned 0. For each call that used datepart that returned 1 meant that the difference between the two date values crossed only and only one interval boundary. For the DATEDIFF function call that used Week for the datepart parameter it didn't cross a week boundary. That is because ‘1980-12-31' is a Wednesday and ‘1981-01-01' is a Thursday. Those two date fall into the same week boundary therefore DATEDIFF returned a 0. It's worth noting that the DATEDIFF function uses Sunday as the first day of the week, and using the SET DATEFIRST command can't change how DATEDIFF determines the first day of the week.
Another date function is DATEADD. This is not a comparison function, but an additive function. This function derives a date value by adding a specific number of date units to a specific date. The syntax for this example is as follows: DATEADD (<datepart>, <number>, <date>).
Note that when the datepart parameter is milliseconds, then any fractional seconds used by the DATEADD function will have a scale of 3 (meaning there are 3 digits after the decimal point when representing milliseconds). When the parameter is microseconds then the scale will be 6, and when the parameter is nanoseconds, the scale is 9. Additionally there could be rounding factors that can affect the outcome of the DATEADD function when using nanoseconds. Since datetime2 values have a scale of 7, you have to add 100 nanoseconds to a datetime2 value in order to see the datetime2 value increase. Because of this SQL server performs some rounding when adding nanoseconds. If the nanoseconds are between 0 and 49 SQL Server rounds down to the nearest 100 nanoseconds. If the nanoseconds is 50 or higher the SQL Server rounds up.
Let's review the code in Listing 4 and the associated results to better understand how the DATEADD function works.
DECLARE @BeginDate1 datetime2 = '1980-12-31 23:59:59.9999999',
@BeginDate2 datetime2 = '1980-12-31 23:59:59.999999',
@BeginDate3 datetime2 = '1980-12-31 23:59:59.999';
SELECT DATEADD(ns,1, @BeginDate1) AS 'Add 1 NS'
,DATEADD(ns,100, @BeginDate1) AS 'Add 100 NS'
,DATEADD(ns,49, @BeginDate1) AS 'Add 49 NS'
,DATEADD(ns,50, @BeginDate1) AS 'Add 50 NS'
,DATEADD(mcs,1, @BeginDate2) AS 'Add 1 MCS'
,DATEADD(ms,1, @BeginDate3) AS 'Add 1 MS'
,DATEADD(Year,1, @BeginDate1) AS 'Add 1 Year';Listing 4: DATEADD examples
When I run the code in Listing 4 I get the results in Result 4. Note results have been formatted for readability.
Add 1 NS Add 100 NS --------------------------- --------------------------- 1980-12-31 23:59:59.9999999 1981-01-01 00:00:00.0000000 Add 49 NS Add 50 NS --------------------------- --------------------------- 1980-12-31 23:59:59.9999999 1981-01-01 00:00:00.0000000 Add 1 MCS Add 1 MS --------------------------- --------------------------- 1981-01-01 00:00:00.0000000 1981-01-01 00:00:00.0000000 Add 1 Year --------------------------- 1981-12-31 23:59:59.9999999
Result 4: Results when Listing 4 is executed
If you look at the code in Listing 4 you will see that the first 4 DATEADD function calls have to do with adding nanoseconds to the date value ‘1980-12-31 23:59:59.9999999'. If you look at the output in Result 4, under the “Add 1 NS”, you will see when I only add 1 nanosecond the functions returns the same value as the date/time value I passed into the function. This is because of rounding, which I will discuss in more detail shortly. If you look at the output under the heading “Add 100 NS” you will see that when I added 100 nanoseconds my date rolled over to the first of year with .0000000 nanoseconds. It is not always the case that you have to add 100 nanoseconds to see the date value change. Due to rounding considerations you could see the date change when less than 100 nanoseconds are added. You can see this rounding in action if you look at headings named “Add 49 NS” and “Add 50 NS” in Result 4. By looking at these two results you can see when I add only 49 nanoseconds the date didn't change, but when I add 50 nanoseconds the date did change, because the nanoseconds were rounded up to the nearest 100. If you explore the code in Listing 4 further you can see when I have a value of ‘1980-12-31 23:59:59.999999' I only need to add 1 microsecond to see the DATEADD function derive the first day of 1981. Alternatively if the datepart parameter is milliseconds then you only need to add 1 millisecond to cause the date to roll over to year 1981. Using DATEADD allows you an easy way to derive a new date based on adding a time interval to a date value.
The next function I will explore is the EOMONTH function. This function was introduced with SQL Server 2012 and returns a date value which represents the last day of the month for a specified date. It also has an optional parameter that can be used to add months to the specified day to determine the last day of the month for future, and/or prior months. This function has the following syntax:
EOMONTH(<start_date>[, <month_to_add>])
Where:
<month_to_add> - Is an integer expression that is added to the start_date. This is an optional parameter. If not specified 0 is used.
Suppose your application needs to know the last day of the prior month, the last day of the current month and last day of the next month when the current date is ‘2015-09-17'. The EOMONTH function could support this with the code in Listing 5.
SELECT EOMONTH ('2015-09-17',-1) AS LastDayOfPriorMonth
,EOMONTH('2015-09-17') AS LastDayOfMonth
,EOMONTH('2015-09-17',1) AS LastDayOfNextMonth
,EOMONTH(getdate()) AS LastDayOfMonth_with_getdate;Listing 5: EOMONTH examples
When I run the code in Listing 5 I get the results in Result 5. Note the results have been reformatted for readability.
LastDayOfPriorMonth LastDayOfMonth
------------------- --------------
2015-08-31 2015-09-30
LastDayOfNextMonth LastDayOfMonth_with_getdate
------------------ ---------------------------
2015-10-31 2015-11-30Result 5: Results when Listing 5 is executed
By reviewing the output in Result 5 you can see I generated 3 different dates. These codes represent the last day of the prior month, last day of the current month and last day of next month based on a start_date parameter value of ‘2015-09-17'. To calculate the LastDayofOPriorMonth I call the EOMONTH function and pass ‘2015-09-17' for the start_date parameter and a -1 for the month_to_add parameter. The LastDayOfMonth value was calculated by only passing a start_date parameter to EOMONTH function, no month_to_add parameter was specified. The next date, LastDayOfNextMonth, was calculated by using the EOMONTH to add 1 month to the start_date parameter. Lastly I showed how the this function (as well as any of the other functions requirement a datetime value) could use the getdate() function to identify the start_date parameter.
Next I will discuss the SWITCHOFFSET function, which was introduced in SQL Server 2008. This function can calculate the date, time and time zone values for a different time zone, based on an existing date and time zone value. This function has the following syntax:
SWITCHOFFSET (<datetimeoffset>, <time_zone>)
Where:
<datetimeoffset> - Is a datetimeoffset expression.
<time_zone> - Is the time zone that will be used to set the offset. The time zone value can be specified as a character string or a signed integer value.
Let's assume I want to know the time in the two time zones adjacent to the current time zone offset, as well as the Greenwich Mean Time. If we assume my current date, time and time zone is “2015-09019 00:00:00.0000000 -07:00” then I can run the code in Listing 6 to identify the date, time and time zone for the the time zones to the east and west of my current time zone, plus the GMT time.
DECLARE @CurrentTimeWithOffset datetimeoffset
= '2015-09-19 00:00:00.0000000 -7:00';
SELECT SWITCHOFFSET(@CurrentTimeWithOffset,'-06:00')
AS TimeZoneToTheEast
,SWITCHOFFSET(@CurrentTimeWithOffset,-480)
AS TimeZoneToTheWest
,SWITCHOFFSET(@CurrentTimeWithOffset,'+00:00')
AS GMTTimeZone;Listing 6: SWITCHOFFSET using different parameter values for time_zone parameter.
When I run the code in Listing 6 I get the results in Result 6. Note results have been reorganized for easier readability.
TimeZoneToTheEast ---------------------------------- 2015-09-19 01:00:00.0000000 -06:00 TimeZoneToTheWest ---------------------------------- 2015-09-18 23:00:00.0000000 -08:00 GMTTimeZone ---------------------------------- 2015-09-19 07:00:00.0000000 +00:00
Result 6: Results when Listing 19 is executed
In Listing 6 I displayed three different date/time/time zone offsets. Each offset was derived by passing a different time_zone offset values. If you look at the code, I first created the local variable name @CurrentTimeWithOffset. This variable is used to hold the current date/time/time zone offset of my machine which is “2015-09-19 00:00:00.0000000 -07:00”. Each time I call the SWITCHOFFSET function I use this variable to identify the datetimeoffset parameter, but I use a different time_zone parameters for each call. For the first function call I passed the literal value “-06:00”. This time_zone parameter value identifies one time zone to the east of my current time zone of “-07:00”. By looking at the Results 6 you can see this first function call created the output column TimeZoneToTheEast, which has a value of “2015-09-19 01:00:00.0000000 -06:00”. To calculate the TimeZoneToTheWestvalue I passed a datetimeoffset signed integer value of “-480”, which has the same meeting a literal string “-08:00”. The last SWITCHOFFSET call just calculates the time and time zone for GMT, which of course has a time zone offset value of “+00:00”.
The next function TODATETIMEOFFSET generates a datetime2 offset value using a datetime2 value and an offset value. Basically this function takes a date and time and then appends a time zone, and uses the following syntax:
TODATETIMEOFFSET ( <expression>, <time_zone>)
Where:
<expression> - Is an expression that equates to a datetime2 value. The function will append the time_zone to this datetime2 value.
<time_zone> - Is the time offset that will be added to the expression parameter. If the value is a character string then the time needs to be in time zone hour and time zone minute format (i.e +01:00) format or it can be a signed integer that represents the time zone in minutes.
To demonstrate how the TODATETIMEOFFSET function will work I will be running the code in Listing 7.
DECLARE @Time datetime2 = '2015-09-19 01:00:00.0000000';
SELECT TODATETIMEOFFSET(@Time,'-01:00') AS TimeZone1
,TODATETIMEOFFSET(@Time,+120) AS TimeZone2;Listing 7: Examples of using TODATETIMEOFFSET
When I run the code in Listing 7 I get the results in Result 7. Note that results have been rearranged for easier readability.
TimeZone1 ---------------------------------- 2015-09-19 01:00:00.0000000 -01:00 TimeZone2 ---------------------------------- 2015-09-19 01:00:00.0000000 +02:00
Result 7: Results when Listing 7 is executed
In the code in Listing 7 I first declared a local variable named @Time with a datetime2 value of “2015-09-19 -1:00:00.0000000”. I then called the TODATETIMEOFFSET function twice, using this @Time variable as a parameter. For the first call I used a time_zone parameter that has the character string value of “-01:00”. If you look at Result 7 you can see the result of the first function call under the header TimeZone1. As you can see the TimeZone1 value in the results is “2015-09-19 01:00:00.0000000 -01:00”. From this example, you can see this function basically just appended the time zone value to the datetime2 value I provided. In my second function call I used a signed integer value of +120 for the time_zone parameter, which is just another method to represent time zone “+02:00”. Review the results in Result 7 to determine what datetimeoffset value I produced when I pass +120 as a time_zone parameter to the TODATETIMEOFFSET function.
Validating Date and Time values with the ISDATE function
There are times when applications need to validate that a character string contains a valid date and/or time. The ISDATE function can be used to validate date, time, date/time and time values. The syntax of the ISDATE function is:
ISDATE (<expression>)
Where:
<expression> - Is a character string that contains a date, time, or date/time value. The expression can be up to 4000 characters long. This function will not accept most date and time data types. The only two date data types you can pass are datetime, OR smalldatetime.
The ISDATE function will return a resultof 1 if the expression parameter contains a valid date. A valid date depends on SET DATEFORMAT setting. To demonstrate the ISDATE function I will run the code in Listing 8.
SET DATEFORMAT 'dmy'; DECLARE @DATE as varchar(4000) = '1-31-1900'; SET NOCOUNT ON; SELECT ISDATE(@DATE) AS ISDATE1; SET DATEFORMAT 'mdy'; SELECT ISDATE(@DATE) AS ISDATE2;
Listing 8: Examples of using ISDATE function
When I run the code in Listing 8 I get the results in Result 8.
ISDATE1 ----------- 0 ISDATE2 ----------- 1
Result 8: Results when Listing 8 is executed
By reviewing the code in Listing 8 you can see that I first set my DATEFORMAT to ‘dmy'. This date format will be used to validate the varchar value I'm passing to the ISDATE function. Next I set a varchar variable @DATE to “1-31-1900”. In the first SELECT statement I call the ISDATE function and pass the parameter @DATE. The result of this first function call can be seen if you look at the ISDATE1 column value in Result 8. Note that this first call returns a 0. A “0” means that an invalid date was past to the ISDATE function. The reason this first call returned a 0 is because the date passed was not in ‘dmy' format. I then change my DATEFORMAT to ‘mdy' and call the ISDATE function a second time. When the DATEFORMAT is ‘mdy' a 1 is returned. Therefore you need to know what your SET DATEFORMAT value is when you determine the format of a date you will be passing to the ISDATE function.
Summary
As you can see there are quite a few different date and time functions that SQL Server can use. These functions not only allow you to manipulate date and time values, but they can also be used to validate whether or not a string contains a valid date. Hopefully next time you have a need for manipulating a date or time value one of these functions will meet that need.
Question and Answer
In this section you can review how well you have understood using the different date and time functions by answering the following questions.
Question 1:
Which datatype definition takes more space to store?
- DATETIME.
- DATETIME2(3)
- DATETIMEOFFSET(3)
Question 2:
When using the DATEDIFF function to compare the seconds between two date/time values what is the maximum difference the two dates can be apart from each other?
- 60 years
- 68 years
- 100 yeas
- There is no limited to how far apart the dates are.
Question 3:
Which version of SQL Server supports EOMONTH function (select all that apply)?
- SQL Server 2005
- SQL Server 2008
- SQL Server 2008 R2
- SQL Server 2012
- SQL Server 2014
Answers:
Question 1:
The correct answer b.
Question 2:
The correct answer is b. When comparing the number of seconds between two dates the maximum difference the two dates can have is 68 years.
Question 3:
The correct answer is d and e. The EOMONTH function came out with SQL Server 2012.