• Steven Willis (3/23/2012)


    Excellent functions. I've already tested them on my own server (2008) and added them to my toolbox of functions. In case you are interested, I added a tiny modification to both functions to handle Day Name Suffixes such as "1st," "2nd," "3rd," and "4th." This code piece can just be added right at the end of the "formattedDate" SELECT statement.

    ...

    Now a mask like this can be used 'DDDD, MMMM DDx, YYYY' to produce 'Friday, March 23rd, 2012'.

    Thank you, Steven. This could be a valuable addition to the code.

    However, I tried to stick to the date format placeholders you can find on this page on MSDN. I tried parsing a date containing the day name suffix with the built-in PARSE function and it did not work.

    Also, the difference between 'hh' and 'h' wasn't clear and didn't seem to produce different results no matter what dates I used for input. Since the 'MM' vs 'M', 'DD' vs 'D', 'HH' vs 'H' all produced the month or day with or without a leading zero respectively, I would assume the same would be the case for 'hh' and 'h'. So I modified the code as:

    WHEN 'h' THEN

    CASE WHEN LEFT(

    RIGHT('00' + CAST(

    CASE DATEPART(hour,@date)

    WHEN 12 THEN 12

    ELSE DATEPART(hour,@date) % 12

    END AS NVARCHAR(2)),2),1) = 0 THEN

    RIGHT('00' + CAST(

    CASE DATEPART(hour,@date)

    WHEN 12 THEN 12

    ELSE DATEPART(hour,@date) % 12

    END AS NVARCHAR(2)),1)

    ELSE

    RIGHT('00' + CAST(

    CASE DATEPART(hour,@date)

    WHEN 12 THEN 12

    ELSE DATEPART(hour,@date) % 12

    END AS NVARCHAR(2)),2)

    END

    Perhaps there is a more efficient way of doing this, but I basically just took the code for 'hh' formatting and turned it into a case statement.

     

    Nice catch, thank you.

    -- Gianluca Sartori