Post reply

Month and Day of Datepart

  • January 4, 2006 at 8:00 am

    #96251

    Hi,

     

    I need to get a two digit day and month out of the datepart and it's only bringing back one digit.

     

    select datepart(mm,getdate())

     

    brings back 1 instead of 01.

     

    Any advice will be appreciated.

     

    Thanks,

  • January 4, 2006 at 9:07 am

    #612643

    I posted the code for this at another site.

    See this link.

    http://sqlobserver.com/community/index.php?topic=57.0

    ------------------------------
    The Users are always right - when I'm not wrong!

  • January 5, 2006 at 2:24 am

    #612805

    select right('0' + convert(varchar,datepart(mm,getdate())),2)

  • January 5, 2006 at 8:40 am

    #612906

    select

    substring(convert(char(10),getdate(),101),1,2)

  • January 5, 2006 at 8:44 am

    #612911

    This is what I did but I think it stinks that the datepart function won't return the two digits even though you specify mm or dd.  Isn't that the reason for what instead of using m or d.

     

    I had to rant a little. Sorry

  • January 5, 2006 at 11:05 am

    #612973

    Well, it is perfectly logical that it doesn't return a leading 0.   The Datepart returns an Integer value.   Integers are usually not expressed with leading 0s!  Which is why the conversion to a string is nescessary.

  • May 28, 2016 at 9:57 pm

    #1881930

    Thank you!

  • May 30, 2016 at 9:42 am

    #1882019

    A new function is available: FORMAT

    declare @d datetime = '2016-05-01';

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

    SELECT FORMAT( @d, 'dd', 'en-US' ) AS 'DateTime Result'

    - BUT:

    Steer clear away from it. People with gazillion bragging points on this forum have cautioned that this is a performance killer if you want your solution to scale.

    So yes, it is unfortunate, but the way to do it is to deal with strings. T-SQL is not really meant to be a formatting tool - such code is often moved to the reporting tool.

Viewing 8 posts - 1 through 7 (of 7 total)

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