SQL Server 2012: Time Marches On

As I get older it seems like time marches on faster and faster. But in reality the passing of time is a constant. Maybe it is because I am get older and I have more memories of dates and times of the past that makes it seem like time passes faster. About now you are probably wondering what the heck my memory of dates and times has to do with SQL Server. Just like I look at my memories based on dates and times, you probably have applications that look at SQL Server data based on dates and times. With the introduction of SQL Server 2012 the Microsoft team has provided some new date and time functions. In this article I will be exploring these new functions, so you can exploit them as you write new application code.

Summary of New Date and Time Functions:

There are seven new date and time functions that where introduced with SQL Server 2012. Here is the list of each of those functions with a brief description of what they do.

With these new date and time functions you can more easily write code to meet different application date and time requirements. In the sections below I will explain each of these functions in more detail and provide you examples of how to use these new functions.

DATEFROMPARTS

Have you ever wanted to produce a date from its parts? If you have, then prior to SQL Server 2012 you might have concatenated the different date parts together into a string and then use the CAST function to convert the concatenated string into a date data type. This could be quite involved and might require a number of CAST clauses if your date parts were integer values. With SQL Server 2012 you no longer need to do this. Instead you can use DATEFROMPARTS function to bring all those date parts together and produce a value that is defined as a date data type. Here is the syntax for the DATEFROMPARTS functions:

DATEPARTS (year, month, day)

Where year, month, and day are integer values.

The following code shows how to build a date value that represents the first day of December 2012 from it parts:

SELECT DATEFROMPARTS(2012, 12, 1) as FirstDayOfDec;

Now that was fairly simple wasn’t it? Here is a way I would have done the same thing prior to SQL Server 2012:

SELECT CAST ('2012' + '12' + '01' as date) as
FirstDayOfDec;

As you can see it is a lot less complicated to write and I think a lot easier to read the code to create a date data type from its parts using the DATEFROMPARTS function available with SQL Server 2012.

DATETIME2FROMPARTS

The new DATETIME2FROMPARTS function allows you to create DATETIME2 data type value using different date parts. Here is the syntax for the DATETIME2FROMPARTS function:

DATETIME2FROMPARTS (year, month, day, hour, minute,
seconds, fractions, precision)

Where, all the parameters (year, month, day, hour, minute, seconds, fractions and precision) all need to be specified as integer values. The fractions and precision parameters are related. The fractions value is the fractional portion of the precision. To show how this works let me provide you with the following two examples.

For this first example I’m creating a DATETIME2 value, which has a precision of 3. Here is the code for this example:

SELECT DATETIME2FROMPARTS (2013, 1, 31, 15, 34, 21, 123, 3);

This example produces the following DATETIME2 value:

2013-01-31 15:34:21.123

Note that the factional seconds has a value of “123”. Here is another similar example:

SELECT DATETIME2FROMPARTS (2013, 1, 31, 15, 34, 21, 23, 3);

When I run this code I get the following output:

2013-01-31 15:34:21.023

In this example because I only passed a 2 digit integer value for the fractional portion of the precision a leading zero was added to the fractions parameter so the fractional value displayed is “023”. If you pass a fractional value that has more digits than the precision value specified, then SQL Server will throw the following error:

Msg 289, Level 16, State 5, Line 1

Cannot construct data type datetime2, some of the arguments have values which are not valid.

DATETIMEFROMPARTS

The DATETIMEFROMPARTS function returns a datetime value when passed the different date parts. Here is the syntax for this function:

DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)

All the parameters (year, month, day, hour, minute, seconds, and milliseconds) need to be specified as integer values.

Here is an example of a SELECT statement that displays the results of a DATETIMEFROMPARTS function:

SELECT DATETIMEFROMPARTS (2013, 10, 30, 9, 37, 11, 123) MyDateTime;

When I run this code I get the following results:

MyDateTime

-- -- -- -- -- -- -- -- -- -- -- -

2013-10-30 09:37:11.123

As you can see here the milliseconds part value is “123”. Because the DATETIME data type only has a precision of 3 milliseconds, when I run the SELECT statement below, which pass “124” for the milliseconds parameter value, I get the same results as above:

SELECT DATETIMEFROMPARTS (2013, 10, 30, 9, 37, 11, 124);

DATETIMEOFFSETFROMPARTS

The DATETIMEOFFSETFROMPARTS function produces a datetimeoffset data type value. A datetimeoffset value contains the date and time and is time zone aware. Here is the syntax for the DATETIMEOFFSETFROMPARTS function:

DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute,
seconds, fractions, hour_offset, minute_offset, precision)

Where, each parameter (year, month, day, hour, minutes, seconds, fractions, hour_offset, minute_offset and precision) must be integer values. The year, month, day, hour, minutes, fractions, and precision are used as describe in the other functions identified so far. But the hour_offset parameter represents the hour portion of the time zone offset, whereas the minute_offset represents the minute portion of the time zone offset. Here is an example of how to use this function to represent 3:30 PM in the Pacific Time zone:

SELECT DATETIMEOFFSETFROMPARTS (2013, 1, 22, 15, 30, 00, 000, -8.00, 00, 0);

EOMONTH

Prior to SQL Server 2012 being released there was lots of different T-SQL code that people developed to calculate the end of month date based on a date value. With the introduction of SQL Server 2012 the Microsoft team decided to create a function to identify a date data type value that represents the end of month. The function is called EOMONTH. With this new function there is no need to use homegrown T-SQL methods to calculate the end of month. Here is the syntax for the EOMONTH function:

EOMONTH (start_date [, month_to_add ])

Where “start_date” is a date value that represents the starting point for calculating the end of month, and “month_to_add” is an optional integer value that represents the number of months to add to the start date before the functions calculate the end of month date.

In the code below I exercise the EOMONTH function to display the prior, current and next month’s end date based on the date “2012-01-16”:

SET NOCOUNT ON;

SELECT EOMonth ('2012-1-16', -1) as PriorMonth;

SELECT EOMonth ('2012-1-16') as CurrentMonth;

SELECT EOMonth ('2012-1-16', +1) as NextMonth;

When I run this code the following output is produced:

PriorMonth

-- -- -- -- -- 

2011-12-31

CurrentMonth

-- -- -- -- -- -- 

2012-01-31

NextMonth

-- -- -- -- -- 

2012-02-29

By reviewing the code you can see that in the first SELECT statement I passed a “-1” for the second parameter. By doing this, one month was subtracted from the date (“2012-1-16”) I passed to the EOMONTH function, so this SELECT statement could produce the end of month date for December 2011. On the second SELECT statement I didn’t pass a second parameter so the default value of 0 was used for the second parameter, which caused this SELECT statement to create the end of month date for January 2012. In the last SELECT statement I passed a “+1” as the second parameter which added one month to the date I passed so I could produce the end of month for February 2012.

SMALLDATETIMEFROMPARTS

The SMALLDATETIMEFROMPARTS function accepts some date/time parts to return a smalldatetime data type value. Here is the syntax for this function:

SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)

Where the year, month, day, hour and minute parameters represent their respective date/time parts and need to be specified as an integer value.

Here is a script that demonstrates how to use the SMALLDATETIMEFROMPARTS function:

SET NOCOUNT ON;

SELECT SMALLDATETIMEFROMPARTS(2013,1,22,11,11) as MyDate;

SELECT SMALLDATETIMEFROMPARTS('2013','1','22','11','11') as MyDate;

Here is the output produced when I run the above SELECT statements:

MyDate

-- -- -- -- -- -- -- -- -- -- -- -

2013-01-22 11:11:00

MyDate

-- -- -- -- -- -- -- -- -- -- -- -

2013-01-22 11:11:00

By reviewing the code you can see you can that I passed both an integer value, or a literal string that represented an integer value required by the SMALLDATETIMEPARTS function. The function will also accept a decimal value for a parameter, as can be shown in the following example:

SET NOCOUNT ON;

SELECT SMALLDATETIMEFROMPARTS(2013.5,1,22,11,11) as MyDate;

SELECT SMALLDATETIMEFROMPARTS('2013.5','1','22','11','11') as MyDate;

Here is the output from the two SELECT statements above:

MyDate

-- -- -- -- -- -- -- -- -- -- -- -

2013-01-22 11:11:00

MyDate

-- -- -- -- -- -- -- -- -- -- -- -

Msg 245, Level 16, State 1, Line 3

Conversion failed when converting the varchar value '2013.5' to data type int.

As you can see the first SELECT statement I passed a decimal value of 2013.5, which executed fine, and truncated this decimal value to 2013 when creating the smalldatetime value. But when I tried to pass ‘2013.5’ as a literal string the function had problems converting this literal string to an integer type value.

TIMEFROMPARTS

The TIMEFROMPARTS function can be used to take a number of time parts and return a time data type. Here is the syntax for this function:

TIMEFROMPARTS (hour, minute, seconds, fractions, precision)

Where, hour, minute, seconds, fractions, and precision are different time parts, which need to be specified as integer expressions.

The precision parameters determine how precise the fractional seconds will be. If 7 is specified then the fractional portion represent nanoseconds, where as if 1 is used the time data type will only be accurate to 10ths of seconds, and if 0 is used then the time date value will always have 0 for the fractional seconds. The fractions parameter is directly related to the precision parameter and it represents the value that will be used for the fractional second portion of the time value. If the precision value is 4 then fractions parameter needs to be a value between 0 and 9999, whereas if the precision value is 5 then the fractions parameter needs to be a value between 0 and 99999. If the precision is 0 then the fractions parameter must be zero. Review the code and results below to better understand the date and time values returned from the TIMEFROMPARTS function:

SET NOCOUNT ON;

SELECT TIMEFROMPARTS(15,29,11,0,4) as MyTime;

SELECT TIMEFROMPARTS(15,29,11,111,4) as MyTime;

SELECT TIMEFROMPARTS(15,29,11,9999,4) as MyTime;

SELECT TIMEFROMPARTS(15,29,11,12345,5) as MyTime;

SELECT TIMEFROMPARTS(15,29,11,0,0) as MyTime;

SELECT TIMEFROMPARTS(15,29,11,1,0) as MyTime;

Here are the results returned when I run the code above:

MyTime

-- -- -- -- -- -- -- -- 

15:29:11.0000

MyTime

-- -- -- -- -- -- -- -- 

15:29:11.0111

MyTime

-- -- -- -- -- -- -- -- 

15:29:11.9999

MyTime

-- -- -- -- -- -- -- -- 

15:29:11.12345

MyTime

-- -- -- -- -- -- -- -- 

15:29:11

MyTime

-- -- -- -- -- -- -- -- 

Msg 289, Level 16, State 2, Line 8

Cannot construct data type time, some of the arguments have values which are not valid.

If you look at the code above you can see that I created results with different precision and fractional parameters. I did this to demonstrate what the fractional portion value looks like for different fractional values for the same precision value. Also note that in the last SELECT statement I passed a non-zero fractions parameter when the precision value is zero. If you look in the results you can see that this SELECT statement produced an error when it executed the TIMEFROMPARTS function.

Exploiting the New Data and Time Functions

The SQL Server team at Redmond has been busy enhancing SQL Server. With the past few releases we have seen them provide a number of new date and time date types and functions. With each new release of SQL Server you owe it to yourself to explore the new features that become available and then exploit them in your code. When you write or enhance your code in the future see if you can exploit these new date and time functions to simplify your T-SQL date and time logic.

See all articles by Greg Larsen