How to convert every column in a table to date time? Mon d yyyy hH:mmAM

Question

I'm trying to update every column in a table to Datetime (yyyy-mm-dd hh:mm:ss.mmm) from mon dd yyyy hh:mmAM. I've wrote a script that affects columns however no column seem to get updated.

UPDATE ParadataDetail
SET Value = Convert(datetime, Value, 120)
WHERE MetaDataId = 29

MySQL or SQL Server? Not every software product that has "SQL" on its name is the same thing. — Álvaro González, Jan 31 '17 at 10:56
Judging by the code I've removed the irrelevant MySql Tag. What is the datatype of the `Value` column? — Zohar Peled, Jan 31 '17 at 10:56
Tag the dbms you're using. (Some product specific SQL there...) — jarlh, Jan 31 '17 at 10:59
[Sql server does not store date display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028), so if the data type of the `Value` column is `DateTime` then your query is meaningless. — Zohar Peled, Jan 31 '17 at 10:59
Value is Varchar(2048). I'm using SQL Server. It needs to be stored as a VarChar in datetime format. — Sam Nicholson, Jan 31 '17 at 10:59
I understand I'm trying to put a datetime into a varchar, the script is obviously wrong — Sam Nicholson, Jan 31 '17 at 11:01
Read Aaron Bertrand's [Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type). Don't keep date values in a varchar column. Change the data type to DateTime. — Zohar Peled, Jan 31 '17 at 11:03
It's an old out dated DB, it's not mine, it's my works. Value isn't always a datetime format. It can have ints. emails etc in, needs to be VarChar(2048). We have a MetaDataId to tell us what value is. 29 is datetime. — Sam Nicholson, Jan 31 '17 at 11:05

score 2 · Accepted Answer · edited Jul 29 '20 at 18:28

Your best option would be to change the data type of the Value column to DateTime. If that's impossible for some reason, and you really, and I do mean really must keep datetime values in a varchar column, and you need to change the string representation of the datetime value, you can use a double conversion:

UPDATE  ParadataDetail
SET Value = Convert(varchar(2048), Convert(datetime, Value, 100), 120)
WHERE MetaDataId = 29

If your sql server version is 2012 or higher, you better use Try_convert:

UPDATE  ParadataDetail
SET Value = Convert(varchar(2048), Try_Convert(datetime, Value, 100), 120)
WHERE MetaDataId = 29

This will set all the values that can't be converted to datetime as null.

I know it a terrible thing to do, it's a generic system for work.This has worked perfectly though, thank you. — Sam Nicholson, Jan 31 '17 at 11:10

How to convert every column in a table to date time? Mon d yyyy hH:mmAM

1 Answers1