This article is contributed. See the original author and article here.
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();
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:
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:
Liliam C Leme
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.