%3CLINGO-SUB%20id%3D%22lingo-sub-1505050%22%20slang%3D%22en-US%22%3EGetdate()%20seems%20wrong%20it%20shows%20UTC...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1505050%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%0A%3CDIV%3ENote%20the%20follow%20commands%20running%20in%20a%20on%20prem%20environment%20will%20return%20to%20you%20the%20date%20and%20time%20of%20your%20server%20region%20configuration%2C%20except%20for%20GetUTCdate%2C%20this%20one%20will%20return%20UTC%20per%20definition%3A%3C%2FDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESELECT%20SYSDATETIME()%26nbsp%3B%0A%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2CSYSDATETIMEOFFSET()%26nbsp%3B%0A%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2CSYSUTCDATETIME()%26nbsp%3B%0A%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2CCURRENT_TIMESTAMP%26nbsp%3B%0A%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2CGETDATE()%26nbsp%3B%0A%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2CGETUTCDATE()%3B%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E(%3C%2FSPAN%3E%3CA%20style%3D%22font-family%3A%20inherit%3B%20background-color%3A%20%23ffffff%3B%22%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Fgetdate-transact-sql%3Fview%3Dsql-server-ver15%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Fgetdate-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Fgetdate-transact-sql%3Fview%3Dsql-server-ver15%3C%2FA%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EAs%20you%20can%20see%20here%20the%20doc%20confirms%3A%20%22%3CEM%3EReturns%20the%20current%20database%20system%20timestamp%20as%20a%26nbsp%3B%3CSTRONG%3Edatetime%3C%2FSTRONG%3E%26nbsp%3Bvalue%20without%20the%20database%20time%20zone%20offset.%20This%20value%20is%20derived%20from%20the%20operating%20system%20of%20the%20computer%20on%20which%20the%20instance%20of%20SQL%20Server%20is%20running.%22%3C%2FEM%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EThis%20is%20my%20on-prem%20instance%20example.%20I%20am%20based%20in%20UTC%2B1%2C%20now%20is%2009%3A15AM.%20So%20the%20results%20make%20sense%20as%20Fig.%201%20shows%3A%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22UTC_onpre.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F203456iF77FB210F6EF9537%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22UTC_onpre.png%22%20alt%3D%22UTC_onpre.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CDIV%20id%3D%22tinyMceEditorLiliam_Leme_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3EFig%201%20OnPrem%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20running%20against%20a%20PaaS%20environment%20such%20as%20Azure%20SQL%20DW.%20The%20same%20will%20show%20UTC%2C%20as%20Fig%202%20shows%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22UTC_Paas.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F203452i852687CD985DD9BA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22UTC_Paas.png%22%20alt%3D%22UTC_Paas.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFig%202%20ADW%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20if%20you%20want%20to%20convert%20to%20your%20timezone%20you%20can%20use%20the%20following.%3C%2FP%3E%0A%3CP%3EFor%20example%20for%20IST%20timezone%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESELECT%20CONVERT(datetime%2C%20'03%2F14%2F2016%2001%3A01%3A00')%0AAT%20TIME%20ZONE%20'India%20Standard%20Time'%0A%0A%0ASELECT%20*%0AFROM%20sys.time_zone_info%0Awhere%20name%20like%20'%25india%25'%0A%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EOther%20very%20good%20links%20about%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-ERR%3AREF-NOT-FOUND-%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Flesson-learned-4-modifying-the-default-time-zone-for-your-local%2Fba-p%2F368798%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Flesson-learned-4-modifying-the-default-time-zone-for-your-local%2Fba-p%2F368798%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-ERR%3AREF-NOT-FOUND-%3CA%20href%3D%22https%3A%2F%2Fthomaslarock.com%2F2016%2F03%2Fsql-server-2016-time%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fthomaslarock.com%2F2016%2F03%2Fsql-server-2016-time%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELiliam%20C%20Leme%3C%2FP%3E%0A%3CP%3EEngineer%20UK%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1505050%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20working%20in%20a%20case%20related%20to%20that%20do%20let%20me%20share%20a%20few%20lines%20here.%20So%20you%20have%20a%20PaaS%20environment%20and%20when%20you%20are%20running%20select%20getdate()%20is%20shows%20the%20wrong%20time%20regards%20your%20timezone.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1505050%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20Administration%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Support%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1514189%22%20slang%3D%22en-US%22%3ERe%3A%20Getdate()%20seems%20wrong%20it%20shows%20UTC...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514189%22%20slang%3D%22en-US%22%3E%3CP%3ECongrats%20on%20publishing%20articles.%20Very%20proud%20of%20it!%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fhtml%2Fimages%2Femoticons%2Fsmile_40x40.gif%22%20alt%3D%22%3Asmile%3A%22%20title%3D%22%3Asmile%3A%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft
Note the follow commands running in a on prem environment will return to you the date and time of your server region configuration, except for GetUTCdate, this one will return UTC per definition:
SELECT SYSDATETIME() 

    ,SYSDATETIMEOFFSET() 

    ,SYSUTCDATETIME() 

    ,CURRENT_TIMESTAMP 

    ,GETDATE() 

    ,GETUTCDATE();
 
 
As you can see here the doc confirms: "Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running."
 
This is my on-prem instance example. I am based in UTC+1, now is 09:15AM. So the results make sense as Fig. 1 shows:
UTC_onpre.png
 

Fig 1 OnPrem

 

If you are running against a PaaS environment such as Azure SQL DW. The same will show UTC, as Fig 2 shows:

UTC_Paas.png

Fig 2 ADW

 

So if you want to convert to your timezone you can use the following.

For example for IST timezone:

 

SELECT CONVERT(datetime, '03/14/2016 01:01:00')
AT TIME ZONE 'India Standard Time'


SELECT *
FROM sys.time_zone_info
where name like '%india%'

Other very good links about this:

 

https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-4-modifying-the-de...

 

https://thomaslarock.com/2016/03/sql-server-2016-time/

 

Liliam C Leme

Engineer UK

2 Comments
Visitor

Congrats on publishing articles. Very proud of it!:smile:

Microsoft

Thanks @Sawamu . You know I highly respect you! I do appreciate this compliment coming from you.