Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : Convert Oracle String Functions to SQL Server String Functions
SQL Server 2012
Home > SQL Server 2012 > New Date Functions
SQL Server 2012 New Date Functions

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 DATETIMEOFFSET value 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 SMALLDATETIME value 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>.
Related Articles :