DATEFROMPARTS function in TSQL

SQL Server 2012 adds a new function called DateFromParts.  This new function simplifies the creating of a DATE type in TSQL over the older ways of doing it. The information here has been extracted from my SQL Saturday presentation on Whats New in TSQL 2012.

Pre-SQL 2012

First lets take a look at how you would do the equivalent to DateFromParts before SQL Server 2012:

DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;

set @month = 4;
set @day = 12
set @year = 2013;
set @TheDate = cast(convert(datetime,convert(varchar(10),@year) + '-' +
 convert(varchar(10),@month) + '-' +
 convert(varchar(10),@day), 101) as date);

select @TheDate;

You could certainly make it work prior to SQL Server 2012, but in SQL 2012 the new DATEFROMPARTS function is available to simplify your query.

SQL 2012 DateFromParts

Here is where it gets easier. Many of the new functions and other TSQL features in SQL Server 2012 appear to just simplify what you could do before. The way that DateFromParts works is like this:

DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;

set @month = 4;
set @day = 12
set @year = 2013;
set @TheDate = DateFromParts(@year, @Month, @Day);

select @TheDate;

Both the old way and the DateFromParts function produce the same results. The DateFromParts function does simplify the amount of typing, and casting and concatenation you need to do to create a date type. Keep in mind that the order of the parameters passed into DateFromParts is always Year, Month, Day independent of the specific country specific date formats you may be using.

A Function to Make DateFromParts Work Prior to SQL Server 2012

If you are not yet on SQL Server 2012 and you would like to start using the DateFromParts function, you could create your own user defined function for now, then just remove it when you get to SQL Server 2012.

If we try the function on SQL Server 2008 as shown below an error will be thrown.

DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;

set @month = 4;
set @day = 12
set @year = 2013;

set @TheDate = DateFromParts(@year, @Month, @Day);

select @TheDate;

To fix that error, all we need to do is create a new scalar valued function called DateFromParts to do the same thing as SQL Server 2012 does:

CREATE FUNCTION DateFromParts
(
@year AS INTEGER,
@month AS INTEGER,
@day AS INTEGER
)
RETURNS [DATE]
AS
BEGIN
RETURN cast(Convert(datetime,convert(varchar(10),@year)+'-'+
            convert(varchar(10),@month)+'-'+
            convert(varchar(10),@day), 101) as date);
END

Then we can run the original query with the function prefixed with dbo to get a similar result on SQL Server 2008 as we would on SQL Server 2012.

DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;

set @month = 4;
set @day = 12
set @year = 2013;

set @TheDate = dbo.DateFromParts(@year, @Month, @Day);

select @TheDate;

So with a simple function you can implement the same DateFromParts function on SQL Server 2008 and 2008R2 that is available on SQL Server 2012. Since the DATE type was not available in SQL 2005, you won’t be able to do this on SQL Server 2005.

Summary

Prior to SQL Server 2012 you could accomplish the same thing as DateFromParts with appropriate casting of variables. SQL Server 2012 introduces the DateFromParts scalar function to simplify things.  If you want to add your own DateFromParts function into SQL Server 2008, or 2008 R2 that can be done easily.

Enjoy!

Related Links

• Stedman Solutions, LLC
• Database Health Monitor

 

---

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!