Cast/Convert nvarchar to datetime

i have table:

id   timestamp
1    31.10.2020 16:32:11
2    09.09.2020 09:15:49
3    22.04.2020 02:48:15

Table have huge amount these data. Column timestamp is data type “nvarchar”. And i need to sort datas by date or use in clauses WHERE for constraint by date, so i need to convert “timestamp” to datetime. But I can’t. I tried convert and cast but still failed.

Any advice?

Thx.

Answer

Fix your design, and change the column to a date and time data type. Considering your data is accurate to 1 second, a datetime2(0) seems appropriate here. First we need to change the “format” of the nvarchar value to an ISO format. We’re going to use the ISO8601 format (yyyy-mm-ddThh:mi:ss.mmm) as it’s unambiguous:

UPDATE dbo.YourTable
SET [timestamp] = CONVERT(nvarchar(20),TRY_CONVERT(datetime2(0), [timestamp], 4), 126);

Now you can ALTER the table and change the data type to a datetime2(0):

ALTER TABLE dbo.YourTable ALTER COLUMN [timestamp] datetime2(0) NULL;

I also recommend using a different name than timestamp for your column. timestamp is a deprecated synonym for rowversion, so it’s use can make it quite confusing; especially as rowversion is not a date and time value but a binary value and cannot be converted to a date and time data type.