asked on

Change time zone returned in Azure SQL database

We have an asp.net C# application with a database that is now hosted in Azure. Of course, the Azure databases return UTC when retrieving any date time values.

I have the following function in SQL

CREATE FUNCTION dbo.EST_GetDateTime()
RETURNS datetime
AS
BEGIN
RETURN(SELECT CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime));
END
GO

--example usage
SELECT dbo.EST_GetDateTime(); GO

Open in new window

Is there a way for me to get the result of the function as the default value in the database?
Or can I get that value in our current statement:
cmdSQL.Parameters.Add("@TranDt", SqlDbType.SmallDateTime).Value = DateTime.Now;

Any help is greatly appreciated!

David Johnson, CD

cmdSQL.Parameters.Add("@TranDt", SqlDbType.SmallDateTime).Value = DateTime.Now will be UTC
you have to convert to your local time zone

Pavel Celba

Use the following conversion:

TimeZoneInfo.ConvertTime(DateTime.Now, TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time"));

Open in new window

Michael Kochilla

ASKER

My mistake.
The code from above is not hit during this particular insert. We have a SqlDataSource in the ASP page code that performs the insert. The TranDt is being assigned by the default value of GetDate() and a datatype of smalldatetime.
Is there a way to handle this default returning the UTC when we need Eastern Standard?
Sorry about the mixup!

Once again, any help is appreciated!

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial