DATEPART("DW", (DT_DBTIMESTAMP)GETDATE()) ==6For those that don't read SSIS Expressions, we start inside the parentheses:
- Get the results of GETDATE()
- Cast that to a DT_DBTIMESTAMP
- Determine the day of the week, DW, for our expression
- Compare the results of all of that to 6
This isn't just an SSIS thing, either. I've seen the above logic in TSQL as well. If you pin your logic to getdate/current_timestamp calls, then your testing is going to be painful.
How do I fix this?
This is best left as an exercise to the reader based on their specific scenario but in general, I'd favor having a step establish a reference date for processing. In SSIS, it could be as simple as a Variable that is pegged to a value when the package begins that you could then override for testing purposes through a SET call to dtexec. Or you could be populating that from a query to a table. Or a Package/Project Variable and have the caller specify the day of the week. For the TSQL domain, the same mechanics could apply - initialize a variable and perform all your tests based on that authoritative date. Provide the ability to specify the date as a parameter to the procedure.
Something, anything really, just take a moment and ask yourself - How am I going to test this?
But, what was the other problem?Looks around... We are not alone. There's a whole other country called Not-The-United-States, or something like that - geography was never my strong suit and damned if their first day of the week isn't Sunday. It doesn't even have to be a different country, someone might have set the server to use a different starting date value for the week (assuming TSQL).
SET LANGUAGE ENGLISH; DECLARE -- 2016 February the 15th @SourceDate date = '20160215' SELECT @@LANGUAGE AS CurrentLanguage , @@DATEFIRST AS CurrentDateFirst , DATEPART(dw, @SourceDate) AS MondayDW; SET LANGUAGE FRENCH; SELECT @@LANGUAGE AS CurrentLanguageFrench , @@DATEFIRST AS CurrentDateFirstFrench , DATEPART(dw, @SourceDate) AS MondayDWFrench;That's not going to affect me though, right? I mean sure, we're moving toward a georedundant trans-bipolar-echolocation-Azure-Amazon cloud computing inframastructure but surely our computers will always be set to deal with my home country's default local, right?
- DATEPART (Transact-SQL)
- SET DATEFIRST
- Bad habits to kick : mis-handling date / range queries Excellent article to read in general but the first section is relevant to dealing with foreign locales.