SQL SERVER 2012 – DateTime Functions – DATEFROMPARTS() – DATETIMEFROMPARTS() – DATETIME2FROMPARTS()

In SQL Server 2012, there are seven new datetime functions being introduced, namely:

• DATEFROMPARTS ( year, month, day)
• DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
• DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
• DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
• SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
• TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
• EOMONTH ()

Today we will quickly look at six of the above functions. I really wanted to write a separate blog post for all of them but once you see an example here you will understand why they are very simple. First six functions have one thing in common among them. They all build datetime value of parameters passed into the function. Let us see the quick example.

SELECT DATEFROMPARTS (2010,12,31) AS Result;
SELECT DATETIME2FROMPARTS (2010,12,31,23,59,59,0,0) AS Result;
SELECT DATETIMEFROMPARTS (2010,12,31,23,59,59,0) AS Result;
SELECT DATETIMEOFFSETFROMPARTS (2010,12,31,14,23,23,0,12,0,7) AS Result;
SELECT SMALLDATETIMEFROMPARTS (2010,12,31,23,59) AS Result
SELECT TIMEFROMPARTS (23,59,59,0,0) AS Result;

Now let us see the result of the above script.

One of the interesting points to note is that in the DATETIME2FROMPARTS, the last parameter is the precision of datetime; it represents the fraction of the second. Here I would like to get back to you with a simple question,

“What is your opinion on these new functions and where exactly will you use them in real life?”

I am looking for creative answers, but each should be close to real-world usage.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)