Serilog - Timestamp in log table has timezone, but ignored by SQL Server

Question

When I cast TimeStamp as a smalldatetime, T-SQL seems to ignore the timezone, so it appears that +00:00 events happened in the future.

What is the proper way to handle the timezone in a timestamp field in a SQL statement?

Example:

2021-09-16 13:05:00.3432122 +00:00 returns 2021-09-16 13:05:00 when cast as smalldatetime, when it actually happened at 9:05am.

2021-09-16 09:05:08.7375111 -04:00 returns the correct time.

SELECT TOP(20) 
    Id, TimeStamp, 
    CAST(TimeStamp AS smalldatetime) AS TimeStampx 
FROM 
    SerilogLogs 
WHERE
    Level = 'Error' 
ORDER BY
    Id DESC;

Results show the +00:00 timestamps haven't had their hour adjusted when cast as smalldatetime: https://i.sstatic.net/OZneR.png

Answer 1

Looks like that Timestamp colum uses the DATETIMEOFFSET type. So you may want to use the SWITCHOFFSET function before casting it to SMALLDATETIME:

SELECT TOP(20) 
    Id, TimeStamp, 
    CAST(SWITCHOFFSET(TimeStamp, '-04:00') AS smalldatetime) AS TimeStampx 
FROM 
    SerilogLogs 
WHERE
    Level = 'Error' 
ORDER BY
    Id DESC;