Datums samenstellen uit delen

Tijdreeksanalyse in SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Datums uit delen

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)

Datum en tijd samen

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

Werken met 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

Valkuilen bij delen

DATEFROMPARTS(1999, 12, NULL)

DATEFROMPARTS(10000, 01, 01)

DATETIME2FROMPARTS(1918, 11, 11, 05, 45, 17, 995, 0)

NULL

Kan gegevenstype date niet construeren; sommige argumenten hebben ongeldige waarden.

Kan gegevenstype datetime2 niet construeren; sommige argumenten hebben ongeldige waarden.

Laten we oefenen!

Tijdreeksanalyse in SQL Server