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 datetime2data 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 populateCREATE 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 valueSELECT * 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 executedSYSDATETIME_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_Valuecolumn was populated with a datetime2value 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 Part Abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y Day dd, d week wk, ww weekday Dw hour Hh minute mi, m second ss, s millisecond Ms microsecond mcs TZoffset Tz ISO_WEEK isowk, isoww
<date> - Is a literal string, expression, user defined variable, or column value that equates to a valid date, smalldatetime, datetime, datetime2or datetimeoffsetdata 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, datetime2or 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 integervalue, 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 integervalue between 1 and 12. This function has the following syntax:
<date> - Is a value is an expression, column value expression, user defined variable, or a literal string that equates to a date, smalldatetime, datetime, datetime2or datetypeoffsetvalue.
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 datetimevalue 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:
<date> - Is an expression that resolves to a datedata 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 datetimevalue 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 datetimevalue 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:Parameter Description year Is an integerexpression that represents the year. month Is an integerexpression that represents the month. day Is an integerexpression that represents the day. hours Is an integerexpression that represents the hour. minutes Is an integerexpression that represents minutes. seconds Is an integerexpression that represents seconds. milliseconds Is a integerexpression that represents the milliseconds. fractions Is an integerexpression that represents fraction of seconds. Needs to be zero if precision is zero. hour_offset Is an integerexpression that identifies the hour offset of the datetimeoffset returned. minute_offset Is an integerexpression that identifies the minute offset of the datetimeoffset returned. precision Is an integerexpression that identifies the precision of the datetimeoffset value returned. Can be a value from 0 to 7. If 0 is used then the <fractions> parameter also needs to zero. 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 2is "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 datetimevalue 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 1column in Result 11. Note that I didn't get a datetimevalue with a millisecond setting of 998. This is because the fractional seconds component of datetimevalues 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:59DATETIMEOFFSETFROMPARTS 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 precisionand fractionswith the same Hour_Offsetand Minute_Offsetvalues. By reviewing output in Result 12 you can see how the different precisionand fractionsvalue 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 fractionsparameter has a value that has more precision then the precisionparameter 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 fractionsparameter because of the requirement when the precisionparameter is 0.
The next function to review is the SMALLDATETIMEFROMPARTS function. When this function is called it returns a smalldatetimevalue. This function has the following syntax: SMALLDATETIMEFROMPARTS (<year>, <month>, <day>, <hour>, <minute>).
The SMALLDATETIMEFROMPARTS function returns a smalldatetimevalue. 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 smalldatetimevalue 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 smalldatetimevalue returned for the two different calls to the SMALLDATETIMEFROMPARTS functions. Note that for each smalldatetimevalue 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 fractionsand precisionvalues. Note that for the first TIMEFROMPART function call I had a fractionsvalue of 998 and a precisionvalue of 7. Here the precision value of 7 specifies that there will be more digits of precision then I specified with the fractionsparameter. Because of this when SQL Server executed the TIMEFROMPARTS function it had to pad zeroes to the left side of the fractionsparameter. 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 fractionsparameter value of 99 and a precisionvalue of 2. Note here I specified the same number of digits in the factionsparameter as I specified for the precisionparameter. It might be worth keeping in mind that you can specify a precisionvalue that supports more precision than the fractionsparameter value specified, but you can not specify a precisionvalue that has less precision than represented by the fractionsparameter. On the last function call I made in Listing 14 to the TIMEFROMPARTS function I specified a 0 for the precisionfunction. Because I did this I also had to have a fractionsparameter value of 0. If the fractionsparameter value was not 0 when I specified a precisionvalue 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:
<strong>What is the difference between the DATEFROMPARTS and DATEPART functions?</strong>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 smalldatetimevalue, without a time zone. The DATETIMEFROMPARTS generates a datetimevalue, but that value doesn't include a time zone. The TIMEFROMPARTS generates a time value without the time zone.