format datetime field

  • Hi, I need to format a datetime field in a query to 'yyyy-mm'. When I run:

    SELECT ((DatePart(year, datefield)) + '-' + (DatePart(month, datefield))) AS PaidMonth

    I only get the year as a result. Can someone point me in the right direction? Thanks.

  • DatePart returns a number, not a string. Use convert to change the datepart to a string.

     

    SELECT convert(varchar(4), DatePart(year, datefield))

    + '-'

    + convert(varchar(2), DatePart(month, datefield))

     

    Let me know if it helped.

  • Thanks ! What would be a good way to ensure the month field has a zero in front of it if it is a singled digit month, so I get '08' instead of 8? Thanks for your help !

  • SELECT convert(varchar, DatePart(year, getdate()))

    + '-'

    + RIGHT('0' + convert(varchar(2), DatePart(month, getdate())), 2)

  • In addition to Remi, IF you know that your field will always be 4 I would change the CONVERT(VARCHAR, to be CONVERT(VARCHAR(4), or CONVERT(CHAR(4),

    I have had instances in the past where a conversion without length has returned only the 1st character .....



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • This seems to do the trick.

    select convert(char(07),getdate(),120)

    ---------------------------------------

    2005-05

     

  • Thanks so much for your help guys !

    One more question. I'm converting an access database to SQL. I have the following query in Access:

    UPDATE XDummyPaidMonth " & _

    " SET PaidMonth = iif([MonthCnt]=49,' PRIOR',Format(DateAdd('m',-[MonthCnt],Date$()),'yyyy-mm'));"

    Would this be its SQL equivalent:

    UPDATE XDummyPaidMonth

    SET PaidMonth = CASE WHEN MonthCnt = 49 THEN 'PRIOR'

    ELSE convert(char(07),(DateAdd(month, -MonthCnt, getdate())),120)

    END

    Thanks again.

  • That looks ok to me... did you test it?

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

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