CODEWITHSUNDEEP
sqlsql-servert-sql
Samuel Dague
Samuel Dague

Reputation: 57

Why am i losing time when converting

Good morning everyone!

I am trying (and failing) and converting times for some reason. What I am trying to do is reach out and grab all records in a table, then every once in a while reach out to the table and grab any new records that have been added to the table after a certain timestamp.

Just for testing purposes I have a table and am doing this manually for now.

my first step in the process is reaching out and grabbing a time that is in UTC format.

SELECT MAX(LastModified) FROM MWOPOL

RESULT: 2020-11-24T10:43:39.694Z

my next step would be converting this time to local time

SELECT CONVERT(VARCHAR(23), SWITCHOFFSET(CONVERT(DATETIMEOFFSET, '2020-11-24T10:43:39.694Z'), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS [LocalTime]

RESULT: 2020-11-24 05:43:39.694

I went with Varchar(23) instead of DATETIME because if I do I lose .001 of the time. when I try to convert the time to local time

SELECT CONVERT(VARCHAR(23),(DATEADD(HOUR,DATEDIFF(HOUR, GETDATE(), GETUTCDATE()),(('2020-11-24 05:43:39.694')))),127) AS [ConvertedBackToUTC]

RESULT: 2020-11-24T10:43:39.693

So when I convert the time back I still end up losing time. which can/will cause issues if there were records added at .694 i end up in a vicious cycle which will cause me to keep adding to the table even if there were new records added.

Upvotes: 0

Views: 364

Answers (1)

Frank
Frank

Reputation: 713

Use DATETIME2 instead of DATETIME. This should work for you:

SELECT CONVERT(DATETIME2(3), SWITCHOFFSET(CONVERT(DATETIMEOFFSET, '2020-11-24T10:43:39.694Z'), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS [LocalTime]

Upvotes: 1

Related Questions