Thursday, September 8, 2011

SQL Denali Format Function

Maybe I'm going crazy or maybe I'm paranoid even though I may think no one is following me but I'm having a heck of a time with the new format function in SQL Denali.  Try this for me:


DECLARE @d DATETIME = GETDATE();

SELECT FORMAT( @d, 'dd', 'en-US' ) AS Result;
SELECT FORMAT( @d, 'd/m/y', 'en-US' ) AS Result;
SELECT FORMAT( @d, 'dd/mm/yyyy', 'en-US' ) AS Result;

My results are the following:

  The rub of the matter is my date is not 8/28/2011.  I ran this on 9/8/2011. What I did do is run this at 8:28 PM at night.  Apparently the format function is grabbing the time value from getdate() and not the date.

I always assume it is not a bug but instead I'm doing something wrong. I would appreciate anyone who has comments or who can correct the problem for me. Just so you know I'm running this on Denali CTP3.

Aaron Bertrand has a most excellent blog on the format function but he doesn't use the getdate function.  I'm open to suggestions, but please stop looking in my windows at night.
Posted by at
Labels: ,

1 comment:

  1. Scott's BlogSeptember 11, 2011 at 6:51 PM

    Kathi Kellenberger pointed out to me that MM is for month and and mm is for minute. Thank you Kathi! So this code provides the expected results.

    DECLARE @d DATETIME = GETDATE();

    SELECT FORMAT( @d, 'dd', 'en-US' ) AS Result;
    SELECT FORMAT( @d, 'd/M/y', 'en-US' ) AS Result;
    SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS Result;

    ReplyDelete

Subscribe to: Post Comments (Atom)