Getting Eastern Standard Time from Azure SQL

Declare @todays_date Date
set @todays_date = CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS date)

For example, if you want the current date and time as a datetime value like GETDATE() does, do this:

CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime)

If you want the current date and time as a datetime2 value like SYSDATETIME() does, do this:

CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime2)

And if you just want the current date, you can do this:

CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS date)