Building dates from parts
Time Series Analysis in SQL Server
Maham Faisal Khan
Senior Data Science Content Developer
Dates from parts
DATEFROMPARTS(year, month, day)
TIMEFROMPARTS(hour, minute, second, fraction, precision)
DATETIMEFROMPARTS(year, month, day, hour, minute, second, ms)
DATETIME2FROMPARTS(year, month, day, hour, minute, second, fraction, precision)
SMALLDATETIMEFROMPARTS(year, month, day, hour, minute)
DATETIMEOFFSETFROMPARTS(year, month, day, hour, minute, second, fraction, hour_offset, minute_offset, precision)
Dates and times together
SELECT
DATETIMEFROMPARTS(1918, 11, 11, 05, 45, 17, 995) AS DT,
DATETIME2FROMPARTS(1918, 11, 11, 05, 45, 17, 0, 0) AS DT20,
DATETIME2FROMPARTS(1918, 11, 11, 05, 45, 17, 995, 3) AS DT23,
DATETIME2FROMPARTS(1918, 11, 11, 05, 45, 17, 9951234, 7) AS DT27;
| DT | DT20 | DT23 | DT27 |
|---|---|---|---|
| 1918-11-11 05:45:17.997 | 1918-11-11 05:45:17 | 1918-11-11 05:45:17.995 | 1918-11-11 05:45:17.9951234 |
Working with offsets
SELECT
DATETIMEOFFSETFROMPARTS(2009, 08, 14, 21,
00, 00, 0, 5, 30, 0) AS IST,
DATETIMEOFFSETFROMPARTS(2009, 08, 14, 21,
00, 00, 0, 5, 30, 0)
AT TIME ZONE 'UTC' AS UTC;
| IST | UTC |
|---|---|
| 2009-08-14 21:00:00 +05:30 | 2009-08-14 15:30:00 +00:00 |
Gotchas when working with parts
DATEFROMPARTS(1999, 12, NULL)
DATEFROMPARTS(10000, 01, 01)
DATETIME2FROMPARTS(1918, 11, 11, 05, 45, 17, 995, 0)
NULL
Cannot construct data type date, some of the arguments have values which are not valid.
Cannot construct data type datetime2, some of the arguments have values which are not valid.
Let's practice!
Time Series Analysis in SQL Server
