0 SQL Server 2012 New Date Functions

SQL Server 2008 introduced 4 new data types associated with the date and time, namely, the DATE, TIME, DATETIME2 and DATETIMEOFFSET data types.  The DATE data types stores only a date value while the TIME data type defines a time of a day without time zone awareness and is based on a 24-hour clock.  The DATETIME2 data type defines a date that is combined with a time of day that is based on a 24-hour clock and has a larger date range, a larger default fractional precision and an optional user-specified precision.  Lastly, the DATETIMEOFFSET data type defines a date that is combined with a time of day that has time zone awareness and is based on a 24-hour clock.

SQL Server 2012, on the other hand, is not introducing any new date and time related data types but is introducing a few new date and time functions that return a date and/or time value from their parts.

Function Name	Description	Syntax	Return Data Type
DATEFROMPARTS	Returns a DATE value for the specified year, month and day.	DATEFROMPARTS( <year>, <month>, <day> )	DATE
DATETIME2FROMPARTS	Returns a DATETIME2 value for the specified date and time and with the specified precision.	DATETIME2FROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <precision> )	DATETIME2
DATETIMEFROMPARTS	Returns a DATETIME value for the specified date and time.	DATETIMEFROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <milliseconds> )	DATETIME
DATETIMEOFFSETFROMPARTS	Returns a DATETIMEOFFSETvalue for the specified date and time and with the specified offsets and precision.	DATETIMEOFFSETFROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <hour_offset>, <minute_offset>, <precision> )	DATETIMEOFFSET
SMALLDATETIMEFROMPARTS	Returns a SMALLDATETIMEvalue for the specified date and time.	SMALLDATETIMEFROMPARTS( <year>, <month>, <day>, <hour>, <minute> )	SMALLDATETIME
TIMEFROMPARTS	Returns a TIME value for the specified time and with the specified precision.	TIMEFROMPARTS( <hour>, <minute>, <seconds>, <fractions>, <precision> )	TIME

Parameters / Arguments :

• <year> - Integer expression specifying a year.
• <month> - Integer expression specifying a month.
• <day> - Integer expression specifying a day.
• <hour> - Integer expression specifying hours.
• <minute> - Integer expression specifying minutes.
• <seconds> - Integer expression specifying seconds.
• <milliseconds> - Integer expression specifying milliseconds.
• <fractions> - Integer expression specifying fractions.  This parameter depends on the <precision> parameter.  For example, if <precision> is 7, then each fraction represents 100 nanoseconds; if <precision> is 3, then each fraction represents a millisecond.
• <hour_offset> - Integer expression specifying the hour portion of the time zone offset.
• <minute_offset> - Integer expression specifying the minute portion of the time zone offset.
• <precision> - Integer literal specifying the precision of the DATETIME2 value to be returned.

In addition to these date/time functions that returns date and time values from their parts, one other new date/time function is the EOMONTH which returns the last day of the month that contains the specified date, with an optional offset.

Function Name	Description	Syntax	Return Data Type
EOMONTH	Returns the last day of the month that contains the specified date, with an optional offset.	EOMONTH( <start_date> [, <month_to_add>] )	Date type of <start_date> or DATETIME2(7)

Parameters / Arguments :

• <start_date> - Date expression specifying the date for which to return the last day of the month.
• <month_to_add> - Optional integer expression specifying the number of months to add to <start_date>.