0

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 ;)

1

I found a nice solution and I will post the solution when i have spare time. It has to do with the t-sql command AT TIME ZONE.

Just seen an answer to that question on linked-in that has just been posted: https://www.linkedin.com/pulse/its-timezone-finally-real-convert-function-salesforce-jack-hobbs/

It is the solution i found a month ago but hadn't had spare time to post it since then.

  • 1
    This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review – Reshma Feb 20 '19 at 11:16
  • 1
    I am the author of the question and found a solution that i will post when i have time. – Johannes Schapdick Feb 20 '19 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.