SQL Server Denali CTP3 has added a bunch of date/time functions.

In the A Quick look at the new EOMONTH function in SQL Server Denali CTP3 post I have already looked at the EOMONTH function. In this post I want to take a look at the DATEFROMPARTS and DATETIMEFROMPARTS functions

DATEFROMPARTS

If you are a .NET programmer then you probably know that you can construct a date by passing a bunch of integers to the DateTime constructor. To create a date of July, 13, 2011 you would do something like this

DateTime date1 = new DateTime(2011, 7, 13);
Console.WriteLine(date1.ToString());

7/13/2011 12:00:00 AM

In SQL Server, you can do something similar now with the DATEFROMPARTS function. Here is what the syntax looks like

DATEFROMPARTS ( year, month, day )

Here is an example

SELECT DATEFROMPARTS ( 2011, 7, 13 ) AS Result;

2011-07-13

If you pass in all ones, you will get year 1, month 1 and day 1

SELECT DATEFROMPARTS(1,1,1)

0001-01-01

You can of course also pass in functions, so to get the first day of the current year and month, you would do this

SELECT DATEFROMPARTS(year(getdate()),month(getdate()),1)

2011-07-01

Here is what BOL has to say about DATEFROMPARTS:

DATEFROMPARTS returns a date value with the date portion set to the specified year, month and day, and the time portion set to the default. If the arguments are not valid, then an error is raised. If required arguments are null, then null is returned.

DATETIMEFROMPARTS

The syntax for DATETIMEFROMPARTS looks like this

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

If you were to pass in the same values as for date into the DATETIMEFROMPARTS function you will get an error

SELECT DATETIMEFROMPARTS(1,1,1)

Msg 174, Level 15, State 1, Line 1

The datetimefromparts function requires 7 argument(s).

It would have been nice that the function returned you the datetime with every thing else as 0 instead of giving an error.

So if you have this in .NET

DateTime date1 = new DateTime(2011, 7, 13, 16, 32, 18, 500);
Console.WriteLine(date1.ToString("M/dd/yyyy h:mm:ss.fff tt"));

7/13/2011 4:32:18.500 PM

You can do this in SQL

SELECT DATETIMEFROMPARTS ( 2011, 7, 13, 16, 32, 18, 500 ) AS Result;

2011-07-13 16:32:18.500

There are a couple of things to be aware of

You have to be within the valid datetime range (January 1, 1753, through December 31, 9999)

SELECT DATETIMEFROMPARTS(1600,1,1,1,1,1,500)

Msg 289, Level 16, State 3, Line 1

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

If you use datetime2, which goes back to year 1 then you are fine, however if you just change DATETIMEFROMPARTS to DATETIME2FROMPARTS you will have a problem, DATETIME2FROMPARTS needs also precision

SELECT DATETIME2FROMPARTS(1600,1,1,1,1,1,500)

Msg 174, Level 15, State 1, Line 1

The datetime2fromparts function requires 8 argument(s).

SELECT DATETIME2FROMPARTS(1600,1,1,1,1,1,500,3)

1600-01-01 01:01:01.500

Here is what BOL has to say about DATETIMEFROMPARTS:

DATETIMEFROMPARTS returns a fully initialized datetime value. If the arguments are not valid, then an error is raised. If required arguments are null, then a null is returned.

Here is what BOL has to say about DATETIME2FROMPARTS:

_DATETIME2FROMPARTS returns a fully initialized datetime2 value. If the arguments are not valid, an error is raised. If required arguments are null, then null is returned. However, if the precision argument is null, then an error is raised.

The fractions argument depends on the precision argument. For example, if precision is 7, then each fraction represents 100 nanoseconds; if precision is 3, then each fraction represents a millisecond. If the value of precision is zero, then the value of fractions must also be zero; otherwise, an error is raised.

Here is a list of some of these new date/time functions