Microsoft® SQL Server® 2012 Bible

196

Part II: Building Databases and Working with Data

New SQL 2012 Functions

This section discusses functions new to SQL Server 2012. You can take a look at the func-

tions and then see the results of some queries.

The EOMonth(start_date, month_to_add) returns a datetime2(7) value. The value

returned is the last date of the month for the specifi ed start date. If the optional value

month to add is provided, the function adds that number of months to the start date and

the last date for the resulting month is returned.

The following example returns the last date of the month for the given start date.

SELECT EOMONTH ( '5/26/1972') AS Result;

Result:

---------

1972-05-31 00:00:00.0000000

In addition to this new function, six new date time functions get date and time values

from their parts. The following section details each function and provides a sample script.

The DateFromParts(year, month, day) function returns a Date value based on the

given values. The following example returns a date value:

SELECT DateFromParts(1972, 5, 26) As Result;

The DateTime2FromParts(year, month, day, hour, minute, seconds, fractions,

precision) function returns a DateTime2 value based on the given values. The following

example returns a datetime2 value:

SELECT DateTime2FromParts(1972, 5, 26, 5, 12, 59, 5, 3) As Results;

The result of the fraction argument is dependent upon the value provided for the preci-

sion. If the precision is set to 7, then the fraction represents 100 nanoseconds. If the

precision is set to 3, then the fraction represents a millisecond. Finally, if the precision is

zero, you must set the fraction to zero; otherwise, an error will be returned.

The DateTimeFromParts(year, month, day, hour, minute, seconds, milliseconds)

function returns a DateTime value based on the given values. The following example

returns a datetime value:

SELECT DateTimeFromParts(1972, 5, 26, 5, 12, 59, 100) As Results;

The DateTimeOffSetFromParts(year, month, day, hour, minute, seconds,

fractions, hour_offset, minute_offset, precision) function returns a

DateTimeOffset value based on the given values. The following example returns a

datetimeoffset value:

SELECT DateTimeOffSetFromParts(1972, 5, 26, 5, 12, 59, 0, 12, 0, 7)

As Results;

c08.indd 196c08.indd 196 7/30/2012 4:21:15 PM7/30/2012 4:21:15 PM

http://www.it-ebooks.info