SQL GetDate() returns wrong time

Question

I am having an issue while using GetDate(), for some reason is not returning the right time (it is 7 hours ahead from the actual time) I am using AZURE and the Database is configured with the right location (West US). I will appreciate any help!

I tried to run this script:

SELECT id,
       status,
       AcceptedDate,
       Getdate(),
       Datediff(hour, AcceptedDate, Getdate())
FROM   orderoffers
WHERE  status = 'Accepted'

http://sqlblog.com/blogs/greg_low/archive/2013/01/20/windows-azure-sql-database-getdate-sysdatetime-sysutcdatetime-and-rounding.aspx — xQbert, Dec 30 '14 at 19:14
To get local from UTC `SELECT GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'` — Don Cheadle, Apr 24 '19 at 15:04

score 9 · Accepted Answer · answered Dec 30 '14 at 19:13

Azure SQL Databases are always UTC, regardless of the data center. You'll want to handle time zone conversion at your application.

In this scenario, since you want to compare "now" to a data column, make sure AcceptedDate is also stored in UTC.

Reference

score 3 · Answer 2 · edited May 29 '16 at 21:31

The SQL databases on the Azure cloud are pegged against Greenwich Mean Time(GMT) or Coordinated Universal Time(UTC) however many applications are using DateTime.Now which is the time according to the regional settings specified on the host machine.

Sometimes this is not an issue when the DateTime is not used for any time spanning or comparisons and instead for display only. However if you migrate an existing Database to SQL Azure using the dates populated via GETDATE() or DateTime.Now you will have an offset, in your case it’s 7 hours during Daylight Saving Time or 8 hours during Standard Time.

score 1 · Answer 3 · edited Nov 19 '20 at 19:49

1

I created a simple function that returns the correct UK time whether in DST or not. It can be adapted for other time zones where DST kicks in.

CREATE FUNCTION [dbo].[f_CurrentDateTime]() RETURNS DATETIME AS
BEGIN
RETURN DATEADD(HOUR,CONVERT(INT,(SELECT is_currently_dst FROM sys.time_zone_info WHERE 1=1 AND NAME = 'GMT Standard Time')),GETDATE())
END

edited Nov 19 '20 at 19:49

F. Müller

3,969
8
38
49

answered Nov 19 '20 at 19:01

Garry G Bain

11
1

score -1 · Answer 4 · answered Mar 11 '19 at 14:22

In this modern times where infrastructure is scaled globally, it is good idea to save data in UTC and convert to a timezone based on users location preference.

Please refere: https://learn.microsoft.com/en-us/dotnet/api/system.datetime.utcnow?view=netframework-4.7.2

SQL GetDate() returns wrong time

4 Answers4