CODEWITHSUNDEEP
sqlsql-servert-sql
Samuel Dague
Samuel Dague

Reputation: 57

Converting datetime not converting datetime

So I have a table and I am trying to convert the time from its current format that looks like this

Sep 17 2020  1:07AM

to

2020-09-16 20:07:00.000

if I try to update the entire table by

UPDATE
OOTYPE 
SET
OOTYPE.LastModified = CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, LastModified), 
DATENAME(TzOffset, SYSDATETIMEOFFSET())))

it runs successfully but the times are still in the format

Sep 17 2020  1:07AM

However, if I just run a simple select statement to show it changed

SELECT CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET,lastmodified), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) FROM [OOTYPE]

The output is exactly what I'm looking for.....

2020-09-16 20:07:00.000

Anyone have any idea where I'm screwing this simple change up at, I have put an embarrassing amount of time into this....

Upvotes: 1

Views: 191

Answers (1)

Emin Mesic
Emin Mesic

Reputation: 1811

If I follow you correctly:

SELECT CONVERT(varchar, CONVERT(datetime, 'Sep 17 2020  1:07AM'), 121)

You should use convert style 121 which is yyyy-mm-dd hh:mi:ss.mmm.

Upvotes: 1

Related Questions