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.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan