Post reply

Exporting Data to Excel File with Yesterday's Date

  • February 9, 2022 at 3:27 pm

    #3981532

    I'm exporting data to an Excel file and adding the date to the end. But I'd like the date to be the prior date.

    The current file name is similar to this: filename_20220209.xlsx.

    I'd like it to be filename_20220208.xlsx.

    Here's the expression I'm using. I tried GETDATE()-1 but that change resulted in an error message. Is there a better way to get the date as formatted above and what is the way to get yesterday's date?

    @[User::VarDestinationFilePath] = @[User::VarArchiveFolder]+Replace(Replace(Replace(Replace(@[User::VarFileName],".","_"+Substring((DT_WSTR,50) GETDATE(),1,10)+"."),"-",""),":","")," ","_")

    Thank you for your help.

  • February 9, 2022 at 3:41 pm

    #3981539

    You just need to use the DATEADD function.

    DATEADD("DAY",-1,GETDATE())

    Try this:

    @[User::VarArchiveFolder]+Replace(Replace(Replace(Replace(@[User::VarFileName],".","_"+Substring((DT_WSTR,50) DATEADD("DAY",-1,GETDATE()),1,10)+"."),"-",""),":","")," ","_")

    EDIT: Also I suggest trying to be more explicit with the date and not use substring on a datetime.

    You can do something like this to get yesterday in YYYYMMDD without the replace and substring.

    YEAR(DATEADD("DAY",-1,GETDATE())) * 10000 + MONTH(DATEADD("DAY",-1,GETDATE())) * 100 + DAY(DATEADD("DAY",-1,GETDATE()))

     

    • This reply was modified 2 years, 10 months ago by  TangoVictor. Reason: Added alternate expression for getting the date

  • February 11, 2022 at 4:21 am

    #3981804

    Thank you for the quick reply. I'll give it a shot. I must have been thinking Excel.

Viewing 3 posts - 1 through 2 (of 2 total)

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