GetDate function in Query Activity

Question

How can we force the GetDate() function used in a query activity to return date and time in Eastern Standard Time?

Here is the query I am currently using. What is happening is that the job is configured to run every 4 hours in automation studio to pull records that were created in the last 4 hours. But since the automation studio is configured in the EST time zone and getdate is not returning a date in EST. The job ends up pulling the same records again sometimes.

Select 
[Email Address] AS [Email Address],
[First Name] AS [First Name],
[Last Name] AS [Last Name],
[Record Type] AS [Record Type],
[Legal Entity] AS [Legal Entity],
[Collected Date] AS [Collected Date],
[Source] AS [Source]
FROM CASL_INS_CONSENT_OPTOUT
WHERE TIMESTAMP  >= DATEADD(hour, - 4, GETDATE())

Answer 1

GetDate() will return the current time on the system. ET runs on CST (non-daylight savings observing), so you will always get back a CST datetime value in your result. Since your date fields were written in EST, you can simply just wrap this in a DateAdd() to add or remove an hour as needed.

Another approach which might be even more precise is to add an automatic datetimestamp field to your Data Extension (check the box next to the Date field in the DE to 'use current date as default'). Your timestamp will then be based on when the record actually arrived in the DE and not when your system recorded the time. This will technically be the most accurate time to query off of, and you could then drop the DateAdd function of adjusting the time by an hour.

Answer 2

I bet the GETDATE function is returning your date in "ET time", which is Central Standard Time, all year round. Then your conversion would be easy using DATEADD.

Answer 3

You may use getdate() AT TIME ZONE 'CENTRAL STANDARD TIME' AT TIME ZONE 'CENTRAL EUROPEAN STANDARD TIME'