Introduction
As a SQL developer, we are always keen to find and understand new features that Microsoft releases with each new version, may be SQL or any other Microsoft packages.
The functions mentioned in the below articles are some of the new T-SQL Date
and Time
functions introduced in SQL 2012. This should help in reducing time in writing some of complicated date time queries in a simple manner.
Using the Code
DATETIME2FROMPARTS
Building on the DATETIME2FROMPARTS
offers more precise DateTime2
data type. It contains fractions of a second to a specified precision. This means the syntax for the function will accommodate additional parameters for fractions and precision. The syntax for this function is DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
.
SELECT DATETIME2FROMPARTS (2014, 02, 18, 14, 23, 44, 50, 2) AS MyDateValue
MyDateValue
2014-02-18 14:23:44.50
DATETIMEFROMPARTS
DATETIMEFROMPARTS
is similar to DATEFROMPARTS
, except it returns a DateTime
value, and not just Date
.
Syntax: DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
. It returns a fully initialized datetime
value, as seen below. If required arguments are NULL
, then a NULL
is returned. However, if the arguments aren't valid, an error is raised similar to previous functions. This function is capable of being remoted to SQL Server 2012 servers and later.
SELECT DATETIMEFROMPARTS (2014, 02, 18, 16, 01, 39, 0) AS MyDateValue
MyDateValue
2014-02-18 16:01:39.000
DATETIMEOFFSETFROMPARTS
The function DATETIMEOFFSETFROMPARTS
returns a datetimeoffset
value for separate integer values of year
, month
, day
, hour
, minutes
, seconds
, fractions
, precision
, and time
offset. The syntax for this function is DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
. The offset arguments represent the time zone offset. The same rules for precision discussed also apply to this function.
SELECT DATETIMEOFFSETFROMPARTS (2014, 02, 18, 14, 30, 00, 5, 12, 30, 1) AS MyDateValue
MyDateValue
2014-02-18 14:30:00.5 +12:30
EOMONTH
EOMONTH
returns the End-Of-Month date for the month of specified date. The syntax of the function is EOMONTH (start_date [, month_to_add])
. The second argument is an optional month_to_add
. This is an integer expression specifying the number of months to add to start_date
before calculating the End-Of-Month date. In other words, month_to_add
is added to start_date
, then the function returns the last day of the month for the resulting date.
If this addition overflows the valid range of dates, an error is raised. This example demonstrates the conventional usage of EOMONTH
, with and without the month_to_add
parameter.
SELECT EOMONTH ('02/18/2014') AS MyDateValue
MyDateValue
2014-02-18
SELECT EOMONTH ('02/18/2014', 5) AS MyDateValue
MyDateValue
2014-02-18
SMALLDATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS
is very similar to DATETIMEFROMPARTS
, except it returns a SmallDateTime
type. The syntax for the function is SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
. If any of the arguments are not valid, an error is thrown. If required arguments are NULL
, then NULL
is returned. This function is capable of being used remotely only on servers with SQL Server 2012 servers or later.
The example shows the conventional use of SMALLDATETIMEFROMPARTS
. Note the resulting value contains both date
and time
. Since "seconds
" isn't an allowed parameter, the answer simply uses "00
" as the default value for seconds.
SELECT SMALLDATETIMEFROMPARTS (2014, 2, 18, 14, 30) AS MyDateValue
MyDateValue
2014-02-18 14:30:00
TIMEFROMPARTS
TIMEFROMPARTS
function returns a fully initialized time
value from a set of integer arguments. Note this function returns only a time
value, not a date
/time
value. The syntax for this function is TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
.
SELECT TIMEFROMPARTS (14, 23, 44, 500, 3) AS MyDateValue
MyDateValue
14:23:44.500
SELECT TIMEFROMPARTS (14, 23, 44.612, 500, 3) AS MyDateValue
MyDateValue
14:23:44.500