Dealing with custom date formats in T-SQL

  • Comments posted to this topic are about the item Dealing with custom date formats in T-SQL

    -- Gianluca Sartori

  • Very nice article. The FORMAT function seems very promising!

    One small remark: maybe include a link to the BOL page of CONVERT you're referring to. (although it's pretty easy to find using Google/Bing :-D)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thank you, Koen.

    The BOL page for CONVERT is linked in the "built-in method so far" section. unfortunately hyperlinks are not easy to spot both on the forums and on the articles. The font is not much different from the regular text and I decided to emphasizes it with bold.

    -- Gianluca Sartori

  • Gianluca Sartori (3/23/2012)


    Thank you, Koen.

    The BOL page for CONVERT is linked in the "built-in method so far" section. unfortunately hyperlinks are not easy to spot both on the forums and on the articles. The font is not much different from the regular text and I decided to emphasizes it with bold.

    Whoops, you're right :blush:

    Apparently I totally missed it. Disregard my statement 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • No mention of ISO date formats?

    If the discussion is around how to present locale-specific date formats I would understand, but when converting dates an international standard must be one of the things that should be considered to detect malformed dates and prevent transposed dates.

    Of course, it might just be easier to get the yanks to comply with the rest of the world in the first place!

  • I think this is a great article, with a lot of tasty code to chew over.

    Thanks for putting in the effort and sharing!

  • surreydude. (3/23/2012)


    No mention of ISO date formats?

    If the discussion is around how to present locale-specific date formats I would understand, but when converting dates an international standard must be one of the things that should be considered to detect malformed dates and prevent transposed dates.

    ISO 8601 dates are handled by SQL Server using the CONVERT function, with styles 126 and 127.

    See here for more detail: http://msdn.microsoft.com/en-us/library/ms187928.aspx

    Of course, it might just be easier to get the yanks to comply with the rest of the world in the first place!

    Well, they did (sort of).

    As a side note, I'm not a "yank". 😛

    -- Gianluca Sartori

  • David McKinney (3/23/2012)


    I think this is a great article, with a lot of tasty code to chew over.

    Thanks for putting in the effort and sharing!

    Thank you, David.

    -- Gianluca Sartori

  • If you want a commercially-supported CLR-based utility for this our SQLUtilities product includes a wide range of date formatting & parsing functions, as well as other useful date/time, string handling and numeric functions (e.g. return a dynamically-generated sequence of dates and/or times as a table-valued function).

    Charles Southey

    www.totallysql.com

    Charles Southey
    www.totallysql.com

  • charles.southey (3/23/2012)


    If you want a commercially-supported CLR-based utility for this our SQLUtilities product includes a wide range of date formatting & parsing functions, as well as other useful date/time, string handling and numeric functions (e.g. return a dynamically-generated sequence of dates and/or times as a table-valued function).

    Charles Southey

    www.totallysql.com

    Charles, I'm not sure this is a good way to promote your product.

    I don't know if the editors are happy with ADs on these forums. Have you checked it with them before posting?

    -- Gianluca Sartori

  • Gianluca Sartori (3/23/2012)


    charles.southey (3/23/2012)


    If you want a commercially-supported CLR-based utility for this our SQLUtilities product includes a wide range of date formatting & parsing functions, as well as other useful date/time, string handling and numeric functions (e.g. return a dynamically-generated sequence of dates and/or times as a table-valued function).

    Charles Southey

    www.totallysql.com

    Charles, I'm not sure this is a good way to promote your product.

    I don't know if the editors are happy with ADs on these forums. Have you checked it with them before posting?

    CozyRoc does it all the time, and they're not banned...

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Yes - it's relevant to the article and provides a possible solution for those grappling with the problem under discussion that they may be looking for. We would never post something that is not directly relevant. Not everyone wants a commercial solution - but I think it's worth knowing that it's there if you do. BTW the product is perpetually free for use on Developer Edition.

    Charles Southey
    www.totallysql.com

  • For those who want to play with the code, you can download it from here:

    SQL Server and Custom Date Formats

    -- Gianluca Sartori

  • interesting article, thanks., but what about just using some variation on

    select replace(convert(varchar(30), getdate(), 103, '/', '\')

    ( i don't have SQL server in front of me to check I've used the right format - I can't recall them all, for which reason i have a couple of UDFs like this at work...)

  • daviesj (3/23/2012)


    interesting article, thanks., but what about just using some variation on

    select replace(convert(varchar(30), getdate(), 103, '/', '\')

    ( i don't have SQL server in front of me to check I've used the right format - I can't recall them all, for which reason i have a couple of UDFs like this at work...)

    Using some string manipulation on built-in CONVERT styles is definitely one of the options, given that you wrap it inside a UDF (you don't want to write all that code every time you need to format, do you?).

    Unfortunately, it could be quite easy to implement for a specific format and nearly impossible for others.

    Moreover, from a performance standpoint, a custom CLR function outperforms all the other methods, REPLACE and variations included. If you really have to format dates, I would strongly recommend using that.

    -- Gianluca Sartori

Viewing 15 posts - 1 through 15 (of 33 total)

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