Exporting Data to Excel File with Yesterday's Date

• callen-557064

  SSC-Addicted

  Points: 460

  More actions

  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.

• TangoVictor

  SSChampion

  Points: 10976

  More actions

  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 3 years, 3 months ago by TangoVictor. Reason: Added alternate expression for getting the date

• callen-557064

  SSC-Addicted

  Points: 460

  More actions

  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.