Link to home
Start Free TrialLog in
Avatar of kaizenpro
kaizenpro

asked on

Current Date/Time SQL Azure

I have just found out that my SQL Azure database time is different to the time on my application code, due to Azure system time being set in a different time zone to my own.

I have a stored procedure on SQL that regularly looks at a table and performs actions on records that are 5 minutes older than the current time - (there is a date time field set during the initial transaction.) The Azure time issue obviously throws this out.

I could manually add a few hours on to my code to change the stored procedure to account for the time difference but this seems messy. I am also in GMT zone which changes to BST during the summer. Any suggestions about how I should account for the difference in time zones??

Thanks
Avatar of kaizenpro
kaizenpro

ASKER

I suppose one solution would be to find out how to convert from one time zone to another.

First I would need to identify the time zone of the posting entry, then the time zone of the Azure database.

Then run a function that works out the difference between the two time zones.

Is this possible?
SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I don't think DST should matter. Whenever you retrieve current time from either the server or the workstation, DST will normally have been included. Of course, at the nights where you shift from 3 to 2 o'clock it may cause some confusion.

That said, this is a common issue, so browsing may reveal some recommended methods that will fit your exact scenario.

/gustav
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I just ran a test from one of our Azure SQL databases:

    select getdate() from sometable

and it returned

    2016-04-24 07:03:02.187

which equals the current UTC time

Still, it doesn't solve your issue with DST unless you filter all dates into normal or summer time.

Perhaps one should store both UTC time, local (or "source") time, and the timezone difference as - in queries - it will be very slow always to have to convert time either to or from UTC.

/gustav
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good point. I just tried GetUtcDate() on Azure SQL and it returns the same as GetDate(), so the do run using UTC.

/gustav
Yes, Azure SQL Databases are always UTC, regardless of the data center. But it is always better to create code reusable for different SQL Server versions so I would rather use GETUTCDATE() in stored procedures and applications than GETDATE().
Thanks for everyone's input. I ultimately decided to use SQL time as standard throughout my application instead of the application's time.
Yep - standardizing on one timezone throughout (generally UTC/GMT is best) is definitely the right approach.

Doug
Whilst the other suggestions work in principle, creating a function which returns the server time and using that across my code seems like a simple solution.