How to convert nvarchar string mm/dd/yyyy hh:mm:ss to datetime?

I have a nvarchar column with dates formatted like this: “04/18/2021 13:04:15” (without quotes). I want to change this column to be datetime and migrate all of it data. I’ve looked for format codes and figured out, that closest code to my variant is U.S (101). So I tried to

UPDATE DropPhotos
SET CreationDateTime = CONVERT(NVARCHAR(255), CONVERT(DateTime, CreationDateTime, 101))
ALTER TABLE DropPhotos
ALTER COLUMN CreationDateTime DateTime

But I get error:

Error converting data type nvarchar to datetime

Answer

First, you should not be storing date/time values as strings in the database. Of course, what you are doing is fine, if the next step is to alter the column to be datetime (or something similar).

Second, you clearly have bad data. So, use try_convert() to find it:

select CreationDateTime
from DropPhotos
where try_convert(DateTime, CreationDateTime, 101) is null and
      CreationDateTime is not null;

You’ll need to figure out what to do about that bad values. If NULL is acceptable, then just use try_convert() in your original logic:

SET CreationDateTime = CONVERT(NVARCHAR(255), TRY_CONVERT(DateTime, CreationDateTime, 101))