Post reply

Create a dynamic date

  • March 3, 2015 at 12:46 am

    #398102

    Want to create a file name including a date, but want the file dated with the previous day's date. I'm currently using the following which gives me todays date, how do I get yesterdays

    @Dest1 + (DT_STR, 4 , 1252)DATEPART( "year" , GETDATE() ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "month" , GETDATE() ) , 2 ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART ( "day" , GETDATE() -1 ) , 2 ) + ".xls"

  • March 3, 2015 at 2:05 am

    #1781280

    Replace GETDATE() with DATEADD(day,-1,GETDATE())

    -- Gianluca Sartori

  • March 3, 2015 at 2:32 am

    #1781289

    A few attempts to get the right syntax, but got there eventually, many thanks

    @Dest1 + (DT_STR, 4 , 1252)DATEPART( "year" , GETDATE() ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "month" , GETDATE() ) , 2 ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "day" , dateadd("day",-1,getdate()) ) , 2 ) + ".xls"

  • March 3, 2015 at 2:49 am

    #1781292

    You're welcome

    -- Gianluca Sartori

  • March 3, 2015 at 8:41 am

    #1781385

    ron.grace 36037 (3/3/2015)

    A few attempts to get the right syntax, but got there eventually, many thanks

    @Dest1 + (DT_STR, 4 , 1252)DATEPART( "year" , GETDATE() ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "month" , GETDATE() ) , 2 ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "day" , dateadd("day",-1,getdate()) ) , 2 ) + ".xls"

    You're only substracting on the day part which means that on April 1st, you'll get 20150400 (speaking of April's fool). And on New Year, you'll get 20160000.

    To avoid that, you need to include the date calculation in every datepart. To make it easier, you can create a variable to store the correct date.

    I'll do it slightly different.

    @[User::Dest1]

    + (DT_WSTR, 8) ((DATEPART( "year" , @[User::PrevDay]) * 10000)

    + (DATEPART( "month" , @[User::PrevDay] ) *100 )

    + DATEPART( "day" , @[User::PrevDay]) ) + ".xls"

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 3, 2015 at 11:50 pm

    #1781534

    Well spotted, didn't think of that

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

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