Changing a nvarchar column into a Date format [clo

问题:

I have a column called DT_APP which contains dates in two formats

1. Sep 4 2012 6:19PM
2. 2013-04-30 23:38.34

I need to convert 1) into 2) in the column but I need it to be in nvarchar(19) as the data type is in nvarchar(19).

I appreciate it should be in Datetime format but Its been set to nvarchar(19).

Thanks,

回答1:

So, assuming that those are the only two formats of your column, then you can do the following:

SELECT CONVERT(NVARCHAR(19),CONVERT(DATETIME,DT_APP,100),120)
FROM YourTable
WHERE DT_APP LIKE '%[aA-zZ]%'

UPDATED

Ok, if you want another column, then you can first create it and then fill the values:

-- First create a new column
ALTER TABLE YourTable
ADD DT_APP2 DATETIME;

-- Fill that column with DATETIME values
UPDATE YourTable
SET DT_APP2 =   CASE WHEN DT_APP LIKE '%[aA-zZ]%'
                THEN CONVERT(DATETIME,DT_APP,100)
                ELSE CONVERT(DATETIME,DT_APP,120) END

After that, you can check the column to see if the values are correct and only then you should delete the DT_APP column.

UPDATE 2 If you just need to update current values, then just do:

UPDATE YourTable
SET DT_APP = CONVERT(NVARCHAR(19),CONVERT(DATETIME,DT_APP,100),120)
WHERE DT_APP LIKE '%[aA-zZ]%'