Create DATETIME and TIME values‏ in SQL Server 2012

Continuing my series on SQL Server 2012, today we will explore how to create DATETIME and TIME values in SQL Server 2012.

While constructing T-sql queries, you may need to create a date using the year, month and day values. In versions prior to SQL Server 2012, you have to use the DATEADD function as shown below

declare @year int, @month int, @day int
select @year=2012, @month=11,@day=19

select dateadd(month,@month-1,dateadd(year,@year-1900,@day-1))

The above creates a date by adding the year, month and day values

In SQL Version 2012, you can make use of the DATEFROMPARTS system function as shown below

declare @year int, @month int, @day int
select @year=2012, @month=11,@day=19

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

Note that it returns a DATE value of datatype DATE which is available from version SQL 2008 onwards. If you want to make it as DATETIME, use CAST function as shown below

declare @year int, @month int, @day int
select @year=2012, @month=11,@day=19

select CAST(DATEFROMPARTS(@year,@month,@day) as DATETIME)

Similarly if you have the time values in hour,minute and second, you can make use of the TIMEFROMPARTS system function

declare @hour int, @minute int, @second int
select @hour=14,@minute=35,@second=29
select TIMEFROMPARTS (@hour,@minute,@second,0,0)

 
The above creates a Time value of TIME datatype. Fourth and fifth arguments of 0 indicates that there are no milliseconds.