|
|
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.
| 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.
| 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>.
|