SundialPreviously, we discussed the SQL Server 2008 data types for storing dates and times.  Today, we would like to discuss the new date-related functions added to SQL Server Denali, the upcoming version of SQL Server which is currently in the preview phase.

There are seven new functions – EOMONTH and six functions that are some version of DATEFROMPARTS.

EOMONTH is a convenient utility function that accepts a value of one of the date types (datetime, datetime2, date, etc) and returns a date of the same the last day of a particular month.  (If the input value is a text field, it returns a datetime2(7).)  For example, GETDATE() returns a datetime, so SELECT EOMONTH(GETDATE()) returns the same type.

SELECT EOMONTH(‘1/2/1983’)
1983-01-31 00:00:00.0000000

SELECT EOMONTH(getdate())
2011-08-31 00:00:00.000

DATEFROMPARTS, et al, provide a much more convenient way to build dates than parsing strings.  In previous versions of SQL Server, if you had variables containing the month, day, and year, for example, you had to use some rather undignified SQL like this to build a date:

SELECT convert(date, CONVERT(char(2), @month) + ‘/’ + convert(char(2), @day) + ‘/’ + convert(char(4), @year))

The new DATEFROMPARTS function is much simpler:

SELECT DATEFROMPARTS(@year, @month, @day)

The six similar functions accept slightly different parameters depending on the return type desired (see table below).  The “precision” parameter is used for types such as the (relatively) new DATETIME2, which have variable numbers of decimal places after the second.  For example, consider:

SELECT DATETIME2FROMPARTS (2011, 8, 15, 23, 59, 59, 1234567, 7)
2011-08-15 23:59:59.1234567

Function Parameters
DATEFROMPARTS (year, month, day)
DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
TIMEFROMPARTS (hour, minute, seconds, fractions, precision)

With DATETIMEOFFSETFROMPARTS, what a DATETIMEOFFSET is because the name is a bit of a misnomer.  It is really a datetime WITH a timezone offset. (I suppose “DATETIMEWITHTIMEZONEOFFSET” was too long of a name?)  This is potentially rather useful.  For example, in a scheduling application, it is nice to be able to show appointments to users in local time.  For instance, you might have a meeting in New York at 9:00 AM and a meeting in California at 5:00 PM, which is 8:00 PM New York time.  The DATETIMEOFFSET type lets you store events in their local times, and then you can use the SWITCHOFFSET function to convert them to a common time for scheduling purposes.  For example, this statement will give us 9:00 AM Eastern Daylight Time:

SELECT DATETIMEOFFSETFROMPARTS (2010, 8, 15, 9, 0, 0, 0, -4, 0, 0)
2010-08-15 09:00:00 -04:00

image credit: Ulf Hinze

Subscribe to our RSS Feed Today!
subscribe to the JASE RSS Feed subscribe to the JASE Email Feed
RSS Feed Daily Email

Comments are closed.