New Date and Time Functions in SQL Server 2012

Problem

SQL Server 2012, has new date and time functions such as DATEFROMPARTS, TIMEFROMPARTS, DATETIMEFROMPARTS, DATETIME2FROMPARTS, SMALLDATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, EOMONTH that can be used to Get Date and Time Values from their parts. In this tip we take a look at some basic examples of how these can be used.

Solution

In this tip we will discuss how to utilize the below new date and time functions available in SQL Server 2012.

The DATEFROMPARTS function, which is available in SQL Server 2012, returns a date value with the date part set to the specified year, specified month and the specified day, and the time portion set to the default as shown in the below query result.

The TIMEFROMPARTS function, which is available in SQL Server 2012, returns a full time value as shown in the below query result.

It is important to note that the fractions argument actually depends on the precision argument.

For example:

1. When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.2. When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.3. When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

The DATETIMEOFFSETFROMPARTS function, which is available in SQL Server 2012, returns a full datetimeoffset data type as shown in the below query result. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

Thanks for the appreciation and I am pleased to know that you looking forward to using New Features of SQL Server 2012 once the product is released. Recently, Microsoft has released the SQL Server 2012 Release Candidate (0). This shows that the SQL Server 2012 RTM will be released in the next 1 or 2 quarters.

I would also recommend you to go through other MSSQLTips on SQL Server 2012 category in your free time.