3

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())
3
  • Can you post your query? Jul 4, 2014 at 10:30
  • See above for details.
    – user10250
    Jul 4, 2014 at 11:33
  • there isnt a need to add aliases if the column names are the same
    – 0xsegfault
    Mar 30, 2017 at 15:23

3 Answers 3

4

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.

1
  • When I use 'use current date as default' it gets same value as GetDate(), -7 hours from my timezone.
    – Bidstrup
    Jan 15, 2016 at 10:55
0

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.

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .