sql - Changing a nvarchar column into a Date format -

i have column called dt_app contains dates in 2 formats

1. sep 4 2012 6:19pm
2. 2013-04-30 23:38.34

i need convert 1) 2) in column need in nvarchar(19) data type in nvarchar(19).

i appreciate should in datetime format been set nvarchar(19).

thanks,

so, assuming 2 formats of column, can following:

select convert(nvarchar(19),convert(datetime,dt_app,100),120) yourtable dt_app '%[aa-zz]%' 

updated

ok, if want column, can first create , fill values:

-- first create new column alter table yourtable add dt_app2 datetime;  -- fill column datetime values update yourtable set dt_app2 =   case when dt_app '%[aa-zz]%'                 convert(datetime,dt_app,100)                 else convert(datetime,dt_app,120) end 

after that, can check column see if values correct , should delete dt_app column.

update 2 if need update current values, do:

update yourtable set dt_app = convert(nvarchar(19),convert(datetime,dt_app,100),120) dt_app '%[aa-zz]%'