Building dates from parts
Analyse de séries temporelles dans 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!
Analyse de séries temporelles dans SQL Server
