I know the function SystemDateToLocalDate is not available inside SQL but i would like exactly that feature inside an SQL query to convert the Date records of data_views (which are CST Central Standard Time) to the dates in a specific timezone with daylight savings (CET - Central European Time and CEST Central European Summer Time).
I didnt found an easy way to do it and stumbled upon various T-SQL date functions.
What i was trying to do is writing SQL Cases that would work like this:
Compare the data of the dataview with:
- lower/earlier than the Last Sunday of March 02:00:00 in CET tranformed to
CST
- ==> Dateadd Hours 7 to the dataview time
- greater than or equal to the Last Sunday of March 03:00:00 CEST tranformed
to CST AND less or equal to the last Sunday of October 02:00:00 CEST
transformed to CST
- ==> Dateadd hours 8 to the dataview time
- You will then have problems because the timeperiod from 02:00:00 to 03:00:00 could have +7 or +8.
- greater than less or equal to the last Sunday of October 02:00:00
transformed to CEST
- ==> Dateadd hours 7 to the dataview time
You could do it with an SSJS and SystemDateToLocalDate but i dont believe that this can handle million of rows as fast as SQL. Especially when the activity is limited to 30min execution time.
Does somebody has an easy or working idea?
If you think i got something wrong in the logic or orthography please also leave a comment or directly edit the post ;)
