13

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.

William Miller
  • 9,839
  • 3
  • 25
  • 46
Poch
  • 369
  • 1
  • 11
  • 24
  • you need to convert on programming side. Just Suggestion. – MRX May 21 '13 at 03:42
  • What DBMS do you use (SQL Server, Oracle ...)? – Mikael Eriksson May 21 '13 at 03:44
  • to_date function or the equivalent in the SQL you're using. – M.Octavio May 21 '13 at 03:47
  • 1
    `How do I convert it to a datetime format like this: "2013-05-21 09:45:48.000"` If you're looking for a specific format, you misunderstand how datetime columns work. They aren't stored in a human-readable format at all, but are merely displayed that way by your query tool for your convenience. – Joel Coehoorn May 21 '13 at 04:12
  • Thanks guys. @MikaelEriksson - I'm using SQL Server 2008. m.octavio: I tried str_to_date, but I get a message that it isn'tr a built-in function :( – Poch May 21 '13 at 04:22

4 Answers4

17

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)
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 2
    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. – Matt Johnson-Pint May 21 '13 at 14:35
  • Thanks for adding Remarks as to where to put the field – Jay Oct 19 '17 at 20:45
4

In SQL Server , cast text as datetime

select cast('5/21/2013 9:45:48' as datetime)
Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133
  • It gives me this error: Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string. :( – Poch May 21 '13 at 05:37
  • 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. – Chris Walsh Oct 02 '20 at 10:16
2

This works:

SELECT STR_TO_DATE(dateColumn, '%c/%e/%Y %r') FROM tabbleName WHERE 1
Junu
  • 49
  • 2
  • This is MySQL, not SQL Server --at least currently. Would be nice though. https://stackoverflow.com/questions/3525593/str-to-date-function-in-sql-server – wibeasley Apr 05 '19 at 00:56
1

In order to convert an existing column with dates stored as varchar, proceed as follows:

Add a new column:

alter table1
    add column_new datetime

Cast the original value:

UPDATE table1
    SET column_new = CAST(column_old AS DATETIME)
Greg Holst
  • 874
  • 10
  • 23