How to convert text column to datetime in SQL

Question

I have a column that's a text:

Remarks (text, null)

A sample value is

"5/21/2013 9:45:48 AM"

How do I convert it to a datetime format like this:

"2013-05-21 09:45:48.000"

The reason for the conversion is that I was trying to get the total number of hours between a datetime column and the date stamp in the Remarks column. I was thinking of something like this:

Remarks (text, null) - Date_Sent (datetime, null)

To be clear, the columns represent the datetime an inquiry by a customer was sent (Date_Sent) and the last response made by a representative regarding the inquiry (Response), so for a sample of a Date_Sent having a value of "2013-05-21 08:00:00.000" and a Response with a value of "5/21/2013 10:00:00 AM", I should get a value of 2.00 (2 hours). Unfortunately, in the database I'm working on, Remarks is a text and Date_Sent is a datetime.

Solution 1

Use convert with style 101.

select convert(datetime, Remarks, 101)

If your column is really text you need to convert to varchar before converting to datetime

select convert(datetime, convert(varchar(30), Remarks), 101)

Solution 2

In SQL Server , cast text as datetime

select cast('5/21/2013 9:45:48' as datetime)

Solution 3

This works:

SELECT STR_TO_DATE(dateColumn, '%c/%e/%Y %r') FROM tabbleName WHERE 1

Comments

[Poch]

It gives me this error: Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string. :(

[Matt Johnson-Pint]

This is the best answer. It will avoid ambiguity when parsing input dates like 1/4/2013. The 101 style is mm/dd/yyyy so it will always be parsed as Jan 4th and never as Apr 1st.

[Jay]

Thanks for adding Remarks as to where to put the field

[wibeasley]

This is MySQL, not SQL Server --at least currently. Would be nice though. stackoverflow.com/questions/3525593/…

[Chris Walsh]

I'm afraid I've downvoted this answer because this cast is not explicitly stating what format the text string is in and so is subject to your current locale settings. For example, from my MSSMS query window, this gives a conversion error as it did with @Poch. The correct solution is to specify explicitly what format you are providing the string date e.g. convert(datetime, convert(varchar(30), Remarks), 101) - the 101 says "MM/DD/YYYY". I prefer to use format 23 which is 'YYYY-MM-DD' (or 120 if inc time). Whichever format you use, by providing the format id, a valid date will always work.