XML FeedsSQL 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
|
Function |
Syntax |
Return value |
Return data type |
|---|---|---|---|
|
|
DATEFROMPARTS ( year, month, day ) |
Returns a date value for the specified year, month, and day. |
|
|
|
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision ) |
Returns a datetime2 value for the specified date and time and with the specified precision. |
|
|
|
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds ) |
Returns a datetime value for the specified date and time. |
|
|
|
DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision ) |
Returns a datetimeoffset value for the specified date and time and with the specified offsets and precision. |
|
|
|
SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) |
Returns a smalldatetime value for the specified date and time. |
|
|
|
TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) |
Returns a time value for the specified time and with the specified precision. |
|
|
|
EOMONTH ( start_date [, month_to_add ] ) |
Returns the last day of the month that contains the specified date, with an optional offset. |
|
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Nice.
I highly doubt Visual FoxPro was the first one to think of SomeFunction(year, month, day) that returns a date, Naomi. 
Great observation, Naomi! But I think Erik is right. Visual FoxPro is not really the type to actually come up with something original lol.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.