Composer des dates à partir d'éléments

Analyse de séries chronologiques dans SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Dates à partir d'éléments

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 et heures ensemble

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

Travailler avec les décalages

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

Pièges avec les éléments

DATEFROMPARTS(1999, 12, NULL)

DATEFROMPARTS(10000, 01, 01)

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

NULL

Impossible de construire le type de données date : certains arguments ont des valeurs non valides.

Impossible de construire le type de données datetime2 : certains arguments ont des valeurs non valides.

Passons à la pratique !

Analyse de séries chronologiques dans SQL Server