Post reply

ISO 8601 - confusion with rendering the date

  • October 6, 2015 at 6:31 am

    #301192

    So since we are working with other branches across the US we are 'adopting' ISO8601 for dates.

    Looking at BOL and posts - I see in general that people say to simply use

    SELECT CONVERT( VARCHAR(34), GETDATE(), 127 ) AS ISO8601, GETDATE() AS SimpleDate

    |ISO8601 |SimpleDate |

    |2015-10-06T08:05:15.250|2015-10-06 08:05:15.250|

    but that does not seem to transmit anything new or really allow for the time zone to come into play , since I always receive essentially the same thing with a T where I previously saw a space.

    Using SELECT SYSDATETIMEOFFSET ( ) AS ISO8601

    |ISO8601 |

    |2015-10-06 08:05:15.2505982 -04:00|

    seems to convey more, but I'm leaning towards

    DECLARE

    @Offset VARCHAR(6);

    SET @Offset = RIGHT( CAST( SYSDATETIMEOFFSET ( ) AS VARCHAR(34)), 6 )

    SELECT @Offset

    SELECT CONCAT( CONVERT( VARCHAR(27), SYSDATETIMEOFFSET(), 126 ), ' ', @Offset ) AS ISO8601

    Yielding

    |ISO8601 |

    |2015-10-06T08:05:15.250 -04:00|

    Which appears to transmit the details I need to convey to the reader.

    So 3 questions

    1. What does everyone else do?

    2. Why isn't this a built-in function?

    3. I'm EST - does -04:00 become -05:00 when daylight savings arrives?

    Doug

  • October 6, 2015 at 9:13 am

    #1831777

    If you're going to work with timezones, you need to use the datetimeoffset data type. This means that you can't rely on GETDATE() because you won't be giving enough information.

    That said, you really should avoid managing dates by using formatted strings. Most systems are able to handle date/time values with their proper data types. If you still need to get the formatted strings, here's an example of the available formats.

    SELECT CONVERT( VARCHAR(40), SYSDATETIMEOFFSET(), 120 ) AS StringDate, 'Code120' AS Code

    UNION ALL

    SELECT CONVERT( VARCHAR(40), SYSDATETIMEOFFSET(), 121 ), 'Code121'

    UNION ALL

    SELECT CONVERT( VARCHAR(40), SYSDATETIMEOFFSET(), 126 ), 'Code126'

    UNION ALL

    SELECT CONVERT( VARCHAR(40), SYSDATETIMEOFFSET(), 127 ), 'Code127'

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply