2

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

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.

  • When I use 'use current date as default' it gets same value as GetDate(), -7 hours from my timezone. – Bidstrup Jan 15 '16 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'

New contributor
Andreea Ionel is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

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.