0

Today is 31 Mar 2021.

In a SSIS expression - DATEPART( "dw", GETDATE() ) - returns 3

Where as in SQL - SELECT DATEPART(dw,GETDATE()) - returns 4

I know that I can use SET DATEFIRST 1; to set the start day as Monday and when the above SQL code is rerun it will show 3. Is there any way to control the start day in SSIS?

variable
  • 8,262
  • 9
  • 95
  • 215
  • 1
    SSIS expressions are VBScript, not T-SQL. Orr rather, halfway between VBScript and VBA. There's no way to control the start of week. What you can do is "shift" the result with eg `(dw +1) %7`. The modulo operation would convert Sunday's 8 to 1 – Panagiotis Kanavos Mar 31 '21 at 09:48
  • If you're using this as part of a larger expression (a comparison, for example), it may be better to use "known good" dates rather than trying to get fixed values. E.g. `DATEPART("dw", GETDATE()) = DATEPART("dw", "20000102")` will always return true on Sundays and false on other days, because 2nd January 2000 was a Sunday. – Damien_The_Unbeliever Mar 31 '21 at 10:32
  • I agree with @PanagiotisKanavos , SSIS data types are different from SQL Server data types. Dates are not handled in the same way in both of them. You can refer to the [following answer for an example](https://stackoverflow.com/questions/54289456/date-calculation-with-parameter-in-ssis-is-not-giving-the-correct-result/54297521#54297521). – Hadi Mar 31 '21 at 11:29
  • Ok so it is guaranteed that it will always start from Monday irrespective of US or UK regional settings? – variable Mar 31 '21 at 16:04

0 Answers0