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