SQL Server 2012, new functions: DATE & TIME FROMPARTS

We have a series of new function in SQL Server 2012. Within the Date and Time area we got both the EOMONTH and a whole bunch of date and/or time from parts functions.

SMALLDATETIMEFROMPARTS( year, month, day, hour, minute )
DATETIMEFROMPARTS( year, month, day, hour, minute, seconds, milliseconds )
DATETIME2FROMPARTS( year, month, day, hour, minute, seconds, fractions, precision )
DATETIMEOFFSETFROMPARTS( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
DATEFROMPARTS( year, month, day )
TIMEFROMPARTS( hour, minute, seconds, fractions, precision )

When is this needed?

If you store date and time with integers, one column per part, take a look at the example below.

DECLARE @table TABLE (
iYear int,
iMonth int,
iDay int,
iHour int,
iMinute int,
iSecond int,
iMilliseconds int,
iFractions int );
DECLARE @timestamp datetime2(7);
SELECT @timestamp = sysdatetime();
INSERT INTO @table(iYear, iMonth, iDay, iHour, iMinute, iSecond, iMilliseconds, iFractions)
SELECT
DATEPART(yyyy,@timestamp), -- Years
DATEPART(mm,@timestamp), -- Months
DATEPART(dd,@timestamp), -- Days
DATEPART(hh,@timestamp), -- Hours
DATEPART(mi,@timestamp), -- Minutes
DATEPART(ss,@timestamp), -- Seconds
DATEPART(ms,@timestamp), -- Milliseconds
DATEPART(ns,@timestamp)/100;
-- The fraction, amount of nanoseconds divided by 100
-- Test the functions
SELECT SMALLDATETIMEFROMPARTS(iYear, iMonth, iDay, iHour, iMinute) FROM @table -- SMALLDATETIME
SELECT DATETIMEFROMPARTS(iYear, iMonth, iDay, iHour, iMinute, iSecond, iMilliseconds) FROM @table -- DATETIME
SELECT DATETIME2FROMPARTS(iYear, iMonth, iDay, iHour, iMinute, iSecond, iFractions, 7) FROM @table -- DATETIME2
SELECT DATETIMEOFFSETFROMPARTS(iYear, iMonth, iDay, iHour, iMinute, iSecond, iFractions, 1, 0, 7) FROM @table -- DATETIMEOFFSET
SELECT DATEFROMPARTS(iYear, iMonth, iDay) FROM @table -- DATE
SELECT TIMEFROMPARTS(iHour, iMinute, iSecond, iFractions, 7) FROM @table -- TIME

SQL Server 2012, new functions: EOMONTH

Have you missed the Oracle function LAST_DAY? Have you solved it in Microsoft SQL Server in various ways?

What we could do before the upcoming release of SQL Server is to start out with the current date, add one month, a8nd subtract the number of days of the current month. Or we could get the current date, add one month, convert that to characters, cut out the year and month part and concatenate that with the string '801', and convert that to date, and then subract one day.

Look no further, now we have the EOMONTH-function instead.

EOMONTH ( start_date [, month_to_add ] ) * start_date is the current date, or the date you want the highest date for * month_to_add is the optional increment of months

-- Prepare a variable with the current date
DECLARE @date date;
SELECT @date = getdate();

-- Method 1
SELECT DATEADD(day, (0 - datepart(day, @date)), DATEADD(month, 1, @date));

-- Method 2
SELECT dateadd(day, -1, CAST(CONVERT(varchar(8), dateadd(month, 1, @date), 120) + '01' AS date));

-- New function
SELECT EOMONTH(@date);
SELECT EOMONTH(@date, 2);
SELECT EOMONTH(@date, -4);

New and changed functions in SQL Server 2012

Microsoft SQL Server 2012 introduces 14 new built-in functions. These functions ease the path of migration for information workers by emulating functionality that is found in the expression languages of many desktop applications. However these functions will also be useful to experienced users of SQL Server.

The new functions are:


Conversion functions
* PARSE
* TRY_CONVERT
* TRY_PARSE

Date and time functions
* EOMONTH
* SMALLDATETIMEFROMPARTS
* DATETIMEFROMPARTS
* DATETIME2FROMPARTS
* DATETIMEOFFSETFROMPARTS
* DATEFROMPARTS
* TIMEFROMPARTS

Logical functions
* CHOOSE
* IIF

String functions
* CONCAT
* FORMAT

In addition to the 14 new functions, one existing function has been changed. The existing LOG function now has an optional second base parameter.

I will try to cover them over the next upcoming blogposts. I start right away with Date and time functions in the next two posts.