Month and Day of Datepart

  • 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,

  • 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!

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

  • select

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

  • 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

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

  • Thank you!

  • 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