In SQL Server 2012, Microsoft introduced seven new datetime functions that provide additional flexibility and convenience when working with dates and times. These functions allow you to easily build datetime values based on the parameters passed into the function.

Let’s take a closer look at six of these functions:

• DATEFROMPARTS ( year, month, day): This function constructs a date value using the specified year, month, and day.
• DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision ): This function constructs a datetime2 value using the specified year, month, day, hour, minute, seconds, fractions, and precision. The precision parameter represents the fraction of a second.
• DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds ): This function constructs a datetime value using the specified year, month, day, hour, minute, seconds, and milliseconds.
• DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision ): This function constructs a datetimeoffset value using the specified year, month, day, hour, minute, seconds, fractions, hour offset, minute offset, and precision.
• SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ): This function constructs a smalldatetime value using the specified year, month, day, hour, and minute.
• TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ): This function constructs a time value using the specified hour, minute, seconds, fractions, and precision.

Here is an example that demonstrates the usage of these functions:

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;

These functions provide a simple and efficient way to construct datetime values based on specific requirements. The DATETIME2FROMPARTS function, in particular, allows you to specify the precision of the datetime value, giving you control over the fraction of a second.

Now, I would like to hear your thoughts on these new functions. How do you think they can be used in real-world scenarios? Feel free to share your creative ideas and examples of where you would utilize these functions.

Remember, these functions are designed to make working with dates and times in SQL Server more convenient and flexible. I look forward to hearing your insights!