Sestavení dat z částí

Time Series Analysis in SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Data z částí

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)

Data a časy dohromady

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

Práce s posuny časového pásma

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

Úskalí při práci s částmi

DATEFROMPARTS(1999, 12, NULL)

DATEFROMPARTS(10000, 01, 01)

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

NULL

Nelze sestavit datový typ date, některé argumenty mají neplatné hodnoty.

Nelze sestavit datový typ datetime2, některé argumenty mají neplatné hodnoty.

Pojďme si procvičit!

Time Series Analysis in SQL Server