I came across something odd today at work.
I was trying to run some retrospective updates using a stored procedure which ran data into a fact table, but couldn’t get it to properly pick up the date I was passing in.
The stored proc takes the relevant date, works out the week from the Time dimension, then runs the update for that week.
The problem was that it wasn’t identifying the week as expected.
It worked fine on our dev environment, but not on the client environment where I was attempting to run the updates.
It turns out the issue was regional settings related to the user I was connected as, on dev it was set to use Monday as the first day of the week
but on the client server it was set to Sunday.
So, I had to rerun the Time dimension population script and add in the following command at the start:
SET DATEFIRST 1
This set the first day of the week to Monday for the session I was using to repopulate the Time dimension and I was then able to run my retrospective updates.