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
