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.

1 comment:

  1. 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