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