Calculate last friday date with timestamp ssis

• dallas13

  SSCertifiable

  Points: 7491

  More actions

  June 17, 2019 at 1:40 pm

  #3654436

  Thanks all. Here is the SSIS expression for those who only need to use SSIS,

  DATEADD("hh",17,(DT_DBTIMESTAMP) ( (DT_STR,30,1252) (DT_DBDATE) (DATEADD("dd", -1 - (DATEPART("dw", getdate()) % 7), getdate()) ) ) )

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  June 17, 2019 at 2:06 pm

  #3654440

  dallas13 wrote:

  Thanks all. Here is the SSIS expression for those who only need to use SSIS, DATEADD("hh",17,(DT_DBTIMESTAMP) ( (DT_STR,30,1252) (DT_DBDATE) (DATEADD("dd", -1 - (DATEPART("dw", getdate()) % 7), getdate()) ) ) )

  If I run this now, I get this:

  15/06/2019 17:00:00

  which, of course, is Saturday. Are you sure that it's working for you?

  ---

• dallas13

  SSCertifiable

  Points: 7491

  More actions

  June 17, 2019 at 2:09 pm

  #3654441

  Mya be you have some different collation or timezone setting. Here is what I am getting.  6/14/2019 5:00:00 PM

• Jeff Moden

  SSC Guru

  Points: 1004596

  More actions

  June 17, 2019 at 4:16 pm

  #3654497

  dallas13 wrote:

  Mya be you have some different collation or timezone setting. Here is what I am getting.  6/14/2019 5:00:00 PM

  My recommendation would be to rewrite it so that it does not use the "DW" DATEPART so that it's agnostic to DATEFIRST and other settings.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)