CODEWITHSUNDEEP
sqlsql-serverdatetype-conversionstring-formatting
Lajos Arpad
Lajos Arpad

Reputation: 76424

String to DateTime in SQL Server

I have a column of varchar type and it contains a lot of data (we are talking about millions of records). I select only the date columns, like this:

select Value from mytable where ISDATE(mytable.Value) = 1

and I want to convert all the values in the result to the format of MM/dd/yyyy HH:mm:ss tt

Note that the values are of many different formats.

How can I do that in SQL Server?

Upvotes: 1

Views: 324

Answers (1)

hgulyan
hgulyan

Reputation: 8239

Try this

SELECT CONVERT(VARCHAR(10), CAST(value as datetime), 101) + ' ' 
     + substring(convert(varchar(20), CAST(value as datetime), 9), 13, 5) 
     + ' ' + substring(convert(varchar(30), CAST(value as datetime), 9), 25, 2) As dateValue
FROM mytable 
WHERE ISDATE(mytable.Value) = 1

More on date formats for SQL SERVER

SQL Server Date Formats

Upvotes: 1

Related Questions