Post reply

Sql Server DateAdd function Hellp

  • January 8, 2009 at 1:08 pm

    #204843

    I am looking to format the prior days date as follows. - 20090107

    I tried the following and could not figure out how to keep the leading zero's

    Select Cast(YEAR(DATEADD("dd", -1, getdate())) as nvarchar(4)) + Cast(MONTH(DATEADD("dd", -1, getdate())) AS nvarchar(2)) + Cast(DAY(DATEADD("dd", -1, getdate())) as nvarchar(2))

    the result is 200917

    Thnaks in advance for your help.

  • January 8, 2009 at 1:22 pm

    #923664

    Why not try

    select convert(varchar(10), dateadd(dd, -1, getdate()), 112)

    Edit Added Convert BOL REF

    http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx

  • January 8, 2009 at 1:49 pm

    #923680

    That works although I neglected to mention that I was using SSIS expression builder and I need to build an expression that shows the leading 0's in the date. That is why I am using dateadd.

  • January 9, 2009 at 1:05 pm

    #924525

    In your expression Put

    (DT_WSTR, 4) DATEPART( "Year", DATEADD("dd", -1, getdate())) + RIGHT("00" + (DT_WSTR, 4) DATEPART( "Month", DATEADD("dd", -1, getdate())),2) + RIGHT("00" + (DT_WSTR, 4) DATEPART( "Day", DATEADD("dd", -1, getdate())),2)

  • January 9, 2009 at 1:08 pm

    #924530

    That works great! Thank you very much for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

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