Basic Date From Parts

• Dates from parts

  • DATEFROMPARTS(year,month,day)
  • TIMEFROMPARTS(hour, minute, second, fraction, precision)
  • DATETIMEFROMPARTS(year, month, day, hour, minute, seconds,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,3) AS DT27;
  

• Working with Offets

        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;
  

---

Translating date strings

• Casting strings

        SELECT
            CAST('09/14/99' AS DATE) AS USDate;
  

• Converting Strings

        SELECT
            CONVERT(DATETIME2(3), 'April 4, 2019 11:52:29.998 PM') AS April14;
  

• Parsing Strings

        SELECT
            PARSE('25 Dezember 2014' AS DATE USING 'de-de') AS Wihnachten;
  

• The cost of parsing

  Function	Conversions Per Second
  CONVERT()	251,997
  CAST()	240,347
  PARSE()	12,620

• Setting Languages

        SET LANGUAGE 'FRENCH'
        DECLARE
            @FrenchDate NVARCHAR(30) = N'18 avril 2019',
            @FrenchNumberDate NVARCHAR(30) = N'18/4/2019';
  
        SELECT
            CAST(@FrenchDate AS DATETIME),
            CAST(@FrenchNumberDate AS DATETIME);
  

---

Working With Offsets

• Anatomy of a DATETIMEOFFSET Components

  Date Part	Example
  Date	2019-04-10
  Time	12:59:02.3908505
  UTC Offset	-04:00

• Changing Offsets

        DECLARE @SomeDate DATETIMEOFFSET = '2019-04-10 12:59:02.3908505 -04:00';
  
        SELECT
            SWITCHOFFSET(@SomeDate, '-07:00') AS LATime;
  

• Converting to DATETIMEOFFSET

        DECLARE @SomeDate DATETIME2(3) = '2019-04-10 12:59:02.390';
  
        SELECT
            TODATETIMEOFFSET(@SomeDate, '-04:00') AS EDT;
  

• Time Zone Swaps with TODATETIMEOFFSET

        DECLARE @SomeDate DATETIME2(3) = '2016-09-04 02:28:29.681';
  
        SELECT
            TODATETIMEOFFSET(
                DATEADD(HOUR, 7, @SomeDate), 
                '_02:00') AS BonnTime;
  

• Discovering time zones

        SELECT
            tzi.name, 
            tzi.current_utc_offset,
            tzi.is_currently_dst
        FROM sys.time_zone_info tzi
        WHERE 
            tzi.name LIKE '%Time Zone%';
  

---

Handling Invalid Dates

• Error-safe date conversion functions

  • “Unsafe” Functions
    • CAST()
    • CONVERT()
    • PARSE()
  • “Safe” Functions
    • TRY_CAST()
    • TRY_CONVERT()
    • TRY_PARSE()

        SELECT
            PARSE('01/13/2019' AS DATE USING 'en-us') AS January13US,
            PARSE('01/13/2019' AS DATE USING 'fr-fr') AS Smarch1FR; 
  

  The above sql statements returns as error while converting string value ‘01/13/2019’ into data type date using culture ‘fr-fr’.

        SELECT
            TRY_PARSE('01/13/2019' AS DATE USING 'en-us') AS January13US,
            TRY_PARSE('01/13/2019' AS DATE USING 'fr-fr') AS Smarch1FR;
  

  The above sql statements will not return an error while converting string value ‘01/13/2019’ into data type date using culture ‘fr-fr’ but instead return NULL as the value for Smarch1FR