Generating DateTime From Year, Month and Day In SQL Server Denali(MSSQL 2011)

SQL Server Denali (MSSQL 2011) has come up with many new functions to generate datetime from different parts of Date Time Like if you have YEAR, MONTH, DAY in different fields you might need a code or function to generate date from these parts. i.e (Year=2011, Month=9 and Day=16) Output Required = 2011-09-16. Or same can be for Time Parts Also if you have Hours, Minutes, Seconds or Milliseconds

So To Achieve These Task we have New Function in Denali (SQL Server 2011). Following are the Functions.

• DATEFROMPARTS
• Syntax :
• DATEFROMPARTS(Year,Month,Day)



Select DATEFROMPARTS(2011,9,16) AS Result

Output :

Result
----------
2011-09-16

• DATETIMEFROMPARTS
• Syntax:
• DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )



Select DATETIMEFROMPARTS ( 2011, 9, 16, 14, 5, 20, 0 ) As Result

Output:

Result
-----------------------
2011-09-16 14:05:20.000

• DATETIMEOFFSETFROMPARTS
• Syntax:
• DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )



SELECT DATETIMEOFFSETFROMPARTS ( 2011, 9,16, 8, 35, 0, 0, 5, 30, 7 ) AS Result;

Output :


Result
----------------------------------
2011-09-16 08:35:00.0000000 +05:30

• SMALLDATETIMEFROMPARTS
• Syntax:
• SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )



Select SMALLDATETIMEFROMPARTS (2011,9, 16, 14, 5 ) As Result

Output :

Result
-----------------------
2011-09-16 14:05:00

• TIMEFROMPARTS
• Syntax:
• TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )



Select TIMEFROMPARTS (23, 59, 59, 0, 0 ) As Result

Output :


Result
----------------
23:59:59