When you build applications that store records in SQL Server you will most likely have to store date and time values as part of the data. To manage all the different date related tasks you might need to perform Microsoft has introduced a number of date functions. In this stairway I will be exploring those date and time functions.

High precision System Date/Time Functions

SQL Server 2014 introduces a number of high precision data/time functions. By "high precision" I mean the time portion of a date/time has an accurate of 100 nanoseconds. Datatypes that included time values that were available in previous versions have much less accuracy and precision.

The first high precision date/time function is SYSDATETIME. This function returns the system date and time for the computer that is running SQL Server. The value returned is a datetime2 data type with a precision of 7. The code in Listing 1 shows how to call this function.

SELECT SYSDATETIME() as SYSDATETIME_Value;

Listing 1: Code to call the SYSDATETIME function

When I run the code in Listing 1 I get the results in Result 1.

SYSDATETIME_Value
---------------------------
2015-08-31 06:19:02.1914694

Result 1: Results when Listing 1 is executed

By reviewing the output in Result 1 you can see that the fractional seconds of the time portion contains 7 digits, or another way to put it the time portion has a precision of 7.

If you want to take the system data and time and place it into column value within a table you can run the code in Listing 2.

SET NOCOUNT ON;
USE tempdb;
GO
-- create table to populate
CREATE TABLE SYSDATETIME_Test (ID int identity(1,1),
                               SYSDATETIME_Value DATETIME2(7));
-- SYSDATETIME() value into column 
INSERT INTO SYSDATETIME_Test(SYSDATETIME_Value) 
SELECT SYSDATETIME();
-- Display inserted value
SELECT * FROM SYSDATETIME_Test;
DROP TABLE SYSDATETIME_Test;

Listing 2: Create/Populate a table column with the value returned from the SYSDATETIME() function

When I run the code in Listing 2 I get the results in Result 2.

Result 2: Results when Listing 2 is executed

SYSDATETIME_Value
---------------------------
2015-08-31 06:19:02.1914694

By reviewing the output in Result 2 you can see that the time portion of the SYSDATETIME_Value column was populated with a datetime2 value that has a precision of 7 digits.

The next high precision system function I will discuss is SYSDATETIMEOFFSET. This system function returns the current server time, and time zone offset. You can see what this function returns by running the code in Listing 3.

SELECT SYSDATETIMEOFFSET() AS SYSDATETIMEOFFSET;

Listing 3: Executing SYSDATETIMEOFFSET

When I run the code in Listing 3 I get the results in Result 3.

SYSDATETIMEOFFSET
----------------------------------
2015-09-01 06:20:40.4243538 -07:00

Result 3: Results when Listing 3 is executed

From reviewing the output in Result 3 you can see that the code in Listing 3 returns a system date where the time portion has 7 digits of precision. Additional output was "-07:00", which is the time zone offset. This time zone value means my local computer time is 7 time zones to the west of Coordinated Universal Time (UTC). UTC is the time on the Greenwich meridian, which is not adjusted daylight savings time.

The last high precision system function is SYSUTCDATETIME. This function returns the UTC time. If you have machines located in a number of different time zones and you have a requirement that all transaction have the same absolute time no matter which machine processed that transaction, then using the value returned from SYSUTCDATETIME will meet this requirement. The example in Listing 4 shows what UTC time is when my machine has a local time that has a time zone off set of "-07:00".

SELECT SYSUTCDATETIME() AS "UTC Time", 
       SYSDATETIME() AS "Local Time"
       DATEADD(HH,-7,SYSUTCDATETIME()) AS "Calculate Local Time”;

Listing 4: Display UTC Time, Local Time and Calculated Local Time

When I run the code in Listing 4 I get the results in Result 4. Note the output has been reformatted for readability.

UTC Time                    Local Time                  
--------------------------- --------------------------- 
2015-09-03 13:18:43.7449138 2015-09-03 06:18:43.7449138 
Calculate Local Time
---------------------------
2015-09-03 06:18:43.7449138

Result 4: Results when Listing 4 is executed

In Results 4 you can see that my current local time on my machine is "06:18:43.7449138", whereas UTC time is 7 hours later with a value of "13:18:43.7449138". Additionally I used the DATEADD function to subtract the 7 hours from UTC time to calculate the local time based off the UTC time.

Lower Precision System Date and Time Functions

There are three system functions that return date/time values with a lower precision than the high precision date and time functions. The lower precision system data and time functions have a fractional second portion that is rounded to an accuracy of one of the following: .000, .003, .007. In Listing 5 below I show you the different values returned from each of these low precision date and time functions.

SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP',
       GETDATE() AS 'GETDATE',
       GETUTCDATE() AS 'GETUTCDATE';

Listing 5: Display low precision Date/Time values

When I run the code in Listing 5 I get the results in Result 5. Note the output has been reformatted for readability.

CURRENT_TIMESTAMP       GETDATE                 
----------------------- ----------------------- 
2015-09-05 07:04:28.123 2015-09-05 07:04:28.123 
GETUTCDATE
-----------------------
2015-09-05 14:04:28.123

Result 5: Results when Listing 5 is executed

By looking at the results in Result 5 you can see that the CURRENT_TIMESTAMP and GETDATE() functions return the same value, which is the current date and time on my machine. There is really no difference between the CURRENT_TIMESTAMP and GETDATE() values. The only real different is the CURRENT_TIMESTAMP function is ANSI SQL compliant. Therefore if you want your code to be ANSI SQL compliant then use CURRENT_TIMESTAMP. If you look at the results of the GETUTCDATE in Result 5 you will see that it didn't return the current time, instead it return the UTC date and time, which in my case is 7 hours later than the current server time.

Functions to work with Date and Time parts

There are times when you might want to get just a piece of the date, like the hour, day, or month. SQL Server provides the following 4 functions for returning different parts of the date:

• DATEPART
• DAY
• MONTH
• YEAR

The DATEPART function returns an integer value for a particular date part. To call this function you need to use the following syntax:

DATEPART(<Date Part>, <date>)

Where:

<Date Part> - Represents the date part you want to return. The <Date Part> value needs to be one of the following different date parts:

<date> - Is a literal string, expression, user defined variable, or column value that equates to a valid date, smalldatetime, datetime, datetime2 or datetimeoffset data value.

To show how the DATAPART function works let me run the code in Listing 6.

DECLARE @TODAY DATETIME = '2015-09-08 06:15:43.390';
SELECT DATEPART(dd,@TODAY) AS 'Day'
      ,DATEPART(mm,@TODAY) AS 'Month'
      ,DATEPART(yy,@TODAY) AS 'Year'
      ,DATEPART(hh,@TODAY) AS 'Hour' 
      ,DATEPART(mi,@TODAY) AS 'Minute'
      ,DATEPART(ss,@TODAY) AS 'Second'
      ,DATEPART(ms,@TODAY) AS 'Millisecond';

Listing 6: Display date parts

When I run the code in Listing 6 I get the results in Result 6. Note the output has been reformatted for readability.

Day         Month       Year        Hour        Minute           
----------- ----------- ----------- ----------- ----------- 
8           9           2015        6           15          
Second      Millisecond
----------- -----------
43          390

Result 6: Results when Listing 6 is executed

By looking at Listing 6 you can see that I called the DATEPART function a number of different times. Each time I called the DATEPART function I used a different date part value. By looking at the output in Result 6 you can see the different date part values returned when I pass the literal string '2015-09-08 06:15:43.390' with different date parts for each call to the DATEPART function.

The next function I'll explain is the DAY function. This function returns the day of the month and has the following calling syntax:

DAY (<date>)

Where:

• <date> - Is a literal string, expression, user defined variable, or column value that equates to a valid date, smalldatetime, datetime, datetime2 or datetimeoffset.
• To show how the DAY function returns the day of the month let's run the code in Listing 7.

SELECT DAY('2015-09-08') AS 'Day of the Month 1' 
      ,DAY('2015-09-12') AS 'Day of the Month 2';

Listing 7: execute DAY function with different date values

When I run the code in Listing 7 I get the results in Result 7.

Day of the Month 1 Day of the Month 2
------------------ ------------------
8                  12

Result 7: Results when Listing 7 is executed

If you review the code in Listing 7 and then the output in Result 7 you will see when the date value is "2015-09-08" the DAY function returns value 8. The reason 8 and not 08 is because the DAY function returns an integer value, and therefore the leading 0 is dropped. When date value is "2015-09-12", the DAY function returns the integer value 12.

The next function I will be describing is the MONTH function. This function returns the month value, as an integer value between 1 and 12. This function has the following syntax:

MONTH (<date>)

Where:

<date> - Is a value is an expression, column value expression, user defined variable, or a literal string that equates to a date, smalldatetime, datetime, datetime2 or datetypeoffset value.

To demonstrate using the MONTH function I will be running the TSQL code in Listing 8.

SELECT MONTH('2015-09-09 05:01') 'Month 1' 
      ,MONTH('17:00') AS 'Month 2';

Listing 8: Execute MONTH function with different date values

When I run the code in Listing 8 I get the results in Result 8.

Month 1     Month 2
----------- -----------
9           1

Result 8: Results when Listing 8 is executed

If you review the results you can see that the first time I called the MONTH function I passed the function a datetime value of '2015-09-09 05:01'. When this value was past to the MONTH function the function returned an integer value of 9. On the second SELECT statement I passed '17:00' to the MONTH function and it returned a month value of 1. The reason it returned 1 is because the MONTH function assumes a date of '1900-01-01' when only a time portion is sent to the month function.

The last function I will review in the section is the YEAR function. This function returns the year value and has the following syntax:

YEAR (<date>)

Where:

<date> - Is an expression that resolves to a date data type.

To demonstrate the YEAR function I'll be running the code in Listing 9.

SELECT YEAR('2015-09-09 05:01') AS 'Year 1' 
      ,YEAR('September 2016') AS 'Year 2';

Listing 9: execute YEAR function with different date values

When I run the code in Listing 9 I get the results in Result 9.

Year 1      Year 2
----------- -----------
2015        2016

Result 9: Results when Listing 9 is executed

If you review the results found in Result 9 you can see when I pass the datetime value of '2015-09-09 05:01', I got back the year value of '2015'. You can also see when I passed the text value of 'September 2016' the function returned a value of '2016'.

Functions to Construct Date and/or Time values from Parts

There are times you want to create a datetime value by putting together the year, month, and day values of a date. With the introduction of SQL Server 2012 there are a number of different functions that will allow you to construct date and time values from date parts. I will go through each of these functions and show you how they work. But first I will discuss the parameters.

In Table 1 is a complete list of the different data and time parameters. I will use these parameters in the examples in this section:

Table 1: List of parameters

As I show you examples below, you can refer to Table 1 to find the descriptions for any of the parameters.

The first function I will be looking at is the DATEFROMPARTS function. This function has the following syntax: DATEFROMPARTS (<year>, <month>, <day>)

To demonstrate the DATEFROMPARTS function I will be running the code in Listing 10.

SELECT DATEFROMPARTS(2015,10,30) AS 'DATEFROMPARTS 1'
      ,DATEFROMPARTS('9999','12','31') AS 'DATEFROMPARTS 2';

Listing 10: Execute DATEFROMPARTS function with parameter date values

When I run the code in Listing 10 I get the results in Result 10.

DATEFROMPARTS 1 DATEFROMPARTS 2
--------------- ---------------
2015-10-30      9999-12-31

Result 10: Results when Listing 10 is executed

If you look at the code in Listing 10 you can see in the first function call to DATEFROMPARTS I passed the integer values 2015, 10 , and 30 as parameter to the DATEFROMPARTS function. If you look at Result 10, you can see this function call returned the date value of "2015-10-30", under the column heading DATEFROMPARTS 1. The result column DATEFROMPART 2 is "9999-12-13" is what was returned from my second call to DATEFROMPARTS function when I passed the following 3 character values: '9999', '12', and '31'.

The next function I will be discussing is the DATETIMEFROMPARTS. This function will return a datetime value and has the following syntax: DATETIMEFROMPARTS (<year>, <month>, <day>, <hour>, <minute>, <seconds>, <milliseconds>)

To demo the DATETIMEFROMPARTS function I will be running the code in Listing 11.

SELECT DATETIMEFROMPARTS(2015,10,30, 13,59,59,998) AS 'DATETIMEFROMPARTS 1',
       DATETIMEFROMPARTS(2015,10,30, 13,59,59,999) AS 'DATETIMEFROMPARTS 2';

Listing 11: Execute DATETIMEFROMPARTS function with parameter date values

When I run the code in Listing 11 I get the results in Result 11.

DATETIMEFROMPARTS 1     DATETIMEFROMPARTS 2
----------------------- -----------------------
2015-10-30 13:59:59.997 2015-10-30 14:00:00.000

Result 11: Results when Listing 11 is executed

In Listing 11 you can see the first time I called the function DATETIMEFROMPARTS I used these parameters: 2015, 10, 30, 13, 10, 12, 998 and I got back a datetime value of '2015-10-30 13:10:12,997'. You can see this by looking at the DATETIMEFROMPART 1 column in Result 11. Note that I didn't get a datetime value with a millisecond setting of 998. This is because the fractional seconds component of datetime values is rounded to an accuracy of one of the following values: .000, .003, and .007. Therefore when I passed in 998 for the millisecond parameter the DATETIMEFROMPARTS had to round to 997. If you look at the second DATETIMEFROMPARTS function call you can see I also got a rounded millisecond value. This time I passed a millisecond value of 999, and it got rounded up to 000. Because of the rounding of milliseconds up, it also caused the second value to also be rounded up to 13, instead of the 12 that I passed to the function.

The next function is DATETIMEOFFSETFROMPARTS. The DATETIMEOFFSETFROMPARTS function returns a datetimeoffset value using the following syntax: DATETIMEOFFSETFROMPARTS(<year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <hour_offset>, <minute_offset>, <precision>).

To help you better understand the different parameters a little better let me run the TSQL found in Listing 12.

SELECT DATETIMEOFFSETFROMPARTS(2015,10,30, 13,59,59,1234567,7,59,7) AS 'DATETIMEOFFSETFROMPARTS 1',
       DATETIMEOFFSETFROMPARTS(2015,10,30, 13,59,59,123,7,59,3) AS 'DATETIMEOFFSETFROMPARTS 2',
       DATETIMEOFFSETFROMPARTS(2015,10,30, 13,59,59,0,7,59,0) AS 'DATETIMEOFFSETFROMPARTS 3';

Listing 12: Execute DATETIMEOFFSETFROMPARTS function with parameter date values

When I run the code in Listing 12 I get the results in Result 12. Note the code in Result 12 has been reformatted for readability.

DATETIMEOFFSETFROMPARTS 1          DATETIMEOFFSETFROMPARTS 2          
---------------------------------- ------------------------------ 
2015-10-30 13:59:59.1234567 +07:59 2015-10-30 13:59:59.123 +07:59
DATETIMEOFFSETFROMPARTS 3
----------------------------------
2015-10-30 13:59:59 +07:59

Result 12: Results when Listing 12 is executed

If you reviewing the code in Listing 12 you will see there are three different DATETIMEOFFSETROMPARTS function calls, where in each call to the DATETIMEOFFSETFROMPARTS function uses a different precision and fractions with the same Hour_Offset and Minute_Offset values. By reviewing output in Result 12 you can see how the different precision and fractions value affected the datetimeoffset values returned from each call to the DATETIMEOFFSETFROMPARTS function. The first datetimeoffset value shown in Result 12 has a fractional seconds value that has a precision of 7, the second one has a precision of 3, and the last one has a precision of 0. It might be worth noting, that if the fractions parameter has a value that has more precision then the precision parameter then the query will fail with a "Cannot construct date type datetimeoffset…" error message. The last DATETIMEOFFSETFROMPARTS function call I passed a value of 0 for the fractions parameter because of the requirement when the precision parameter is 0.

The next function to review is the SMALLDATETIMEFROMPARTS function. When this function is called it returns a smalldatetime value. This function has the following syntax: SMALLDATETIMEFROMPARTS (<year>, <month>, <day>, <hour>, <minute>).

The SMALLDATETIMEFROMPARTS function returns a smalldatetime value. With this function you can only specify the time portion down the minute. Therefore the function will always return 00 for the second's value of the smalldatetime value returned. Let's review a couple of examples.

SELECT SMALLDATETIMEFROMPARTS ( 2015, 09, 15, 23, 59 ) 
                                     AS FirstSmallDateTime 
      ,SMALLDATETIMEFROMPARTS ( 2015, 09, 30, 23, 59 ) 
                                     AS SecondSmallDateTime;

Listing 13: Execute SMALLDATETIMEFROMPARTS function with parameter values

When I run the code in Listing 13 I get the results in Result 13.

FirstSmallDateTime      SecondSmallDateTime
----------------------- -----------------------
2015-09-15 23:59:00     2015-09-30 23:59:00

Result 13: Results when Listing 13 is executed

If you review the output in Result 13 you will see a smalldatetime value returned for the two different calls to the SMALLDATETIMEFROMPARTS functions. Note that for each smalldatetime value returned the seconds are set to "00". Additionally if you send an invalid value for one or more of the parameters to the function then you will get an error. As an example passing a day value of 31 when you pass a 09 from the month would produce a parameter validation error because there is not a day 31 in the month of September.

The last of the date part functions is TIMEFROMPARTS. This function allows you to create a time data type from parts. This function has the following syntax: TIMEFROMPARTS (<hour>, <minute>, <seconds>, <fractions>, <precision>).

To better understand the TIMEFROMPARTS function let me run the code in Listing 14.

SELECT TIMEFROMPARTS(13,59,59,998,7) 'TIMEFROMPARTS 1', 
       TIMEFROMPARTS(13,59,59,99,2) 'TIMEFROMPARTS 2',
       TIMEFROMPARTS(13,59,59,9980000,7) 'TIMEFROMPARTS 3',
       TIMEFROMPARTS(13,59,59,0,0) AS 'TIMEFROMPARTS 4';

Listing 14: Execute TIMEFROMPARTS function with parameter values

When I run the code in Listing 14 I get the results in Result 14.

TIMEFROMPARTS 1  TIMEFROMPARTS 2  TIMEFROMPARTS 3  TIMEFROMPARTS 4
---------------- ---------------- ---------------- ----------------
13:59:59.0000998 13:59:59.99      13:59:59.9980000 13:59:59

Result 14: Results when Listing 14 is executed

By reviewing the code in Listing 14 you can see that I'm calling the TIMEFROMPARTS function 4 different times. For each function call, I specify a different fractions and precision values. Note that for the first TIMEFROMPART function call I had a fractions value of 998 and a precision value of 7. Here the precision value of 7 specifies that there will be more digits of precision then I specified with the fractions parameter. Because of this when SQL Server executed the TIMEFROMPARTS function it had to pad zeroes to the left side of the fractions parameter. It padded enough zeroes to make the fraction 7 digits long. If you look at the second TIMEFROMPARTS function call you will see I specified a fractions parameter value of 99 and a precision value of 2. Note here I specified the same number of digits in the factions parameter as I specified for the precision parameter. It might be worth keeping in mind that you can specify a precision value that supports more precision than the fractions parameter value specified, but you can not specify a precision value that has less precision than represented by the fractions parameter. On the last function call I made in Listing 14 to the TIMEFROMPARTS function I specified a 0 for the precision function. Because I did this I also had to have a fractions parameter value of 0. If the fractions parameter value was not 0 when I specified a precision value of 0 then I would have gotten an error.

Summary

Generating date and time values isn't really an advanced topic, but there are many subtleties you need to be aware of. When picking and using a date/time function you need to make sure you understand the rounding issues that might occur, as well as the precision of the date/time values generated. Understanding the issues will help you determine the correct function and calling parameters the next time you need to generate a date/time value from one of the functions I explored in this level.

Question and Answer

In this section you can review how well you have understood using the different date and time function by answering the following questions.

Question 1:

What is the difference between the DATEFROMPARTS and DATEPART functions?

• The DATEFROMPARTS function returns a date part value and the DATEPART function generates a date value from parts.
• The DATEFROMPARTS function generates a date value from parts and the DATEPART function returns a date part value.
• The DATEFROMPARTS function determines if date parts are valid and DATEPART returns a date value.
• The DATEFROMPARTS function returns a date part value, and DATEPART determines if the date part is valid.

Question 2:

Which functions could have rounding issues (Choose all that apply)?

• DATETIMEFROMPARTS
• DATETIMEOFFSETFROMPARTS
• SMALLDATETIMEFROMPARTS
• TIMEFROMPARTS

Question 3:

Which of these functions generates a date and time value that contains a time zone?

• SMALLDATETIMEFROMPARTS
• DATETIMEFROMPARTS
• DATETIMEOFFSETFROMPARTS
• TIMEFROMPARTS

Answers:

Question 1:

The correct answer b.

Question 2:

The correct answer is a. DATETIMEFROMPARTS is the only function that rounds the fractional seconds. It rounds to one of the following: .000, .003, .007..

Question 3:

The correct answer is c. The SMALLDATETIMEFROMPARTS generates a smalldatetime value, without a time zone. The DATETIMEFROMPARTS generates a datetime value, but that value doesn't include a time zone. The TIMEFROMPARTS generates a time value without the time zone.