SQL Server DateTime with timezone

Question

I would like to show datetime in following format with TimeZone associated:

7/19/2017 10:15:55 AM CST 

Is it possible to show this in SQL Server?

Thanks.

Answer 1

try this query ;

select CONVERT(VARCHAR,GETDATE(),1) + ' ' + RIGHT(CONVERT(VARCHAR,GETDATE(),9), 14)

if you wanted to add timezone try this :

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT


select CONVERT(VARCHAR,GETDATE(),1) + ' ' + RIGHT(CONVERT(VARCHAR,GETDATE(),9), 14) + ' ' + @TimeZone

Answer 2

Based on on your question please try datetimeoffset:

Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock. source

DECLARE @datetimeoffset datetimeoffset(4) = getdate();  
SELECT @datetimeoffset AS 'CurrentTimeAndLocation'

Result example:

CurrentTimeAndLocation
2017-07-20 09:03:48.7270 +00:00

Also if you would like to play with dates and time data types and functions please take at look here