String to DateTime in SQL Server

Question

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?

Do you know all of the possible date formats ahead of time? I'd think you need to parse all the strings into an intermediate DATETIME object and then pull the string back out using your display format. — Mike Parkhill
– Mike Parkhill, Commented Oct 14, 2012 at 20:04
Why not convert dates from the UI instead consuming DB Server resources? — ron tornambe
– ron tornambe, Commented Oct 14, 2012 at 20:24
@Ron, what kind of UI are you talking about? I have millions of already stored date values unfortunately stored as varchars and I want to convert them to a unified format. — Lajos Arpad
– Lajos Arpad, Commented Oct 14, 2012 at 20:27

hgulyan · Accepted Answer · 2012-10-15 09:44:05Z

1

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

6 Comments

hgulyan Over a year ago

@RichardTheKiwi Reason is that he needs it in "MM/dd/yyyy HH:mm:ss tt" Can you do you that without breaking day and time? Please, add your answer, if you can.

hgulyan Over a year ago

@RichardTheKiwi You've deleted your comment? What about downvote? Can you make your point?

Lajos Arpad Over a year ago

thank you, I figured out the remaining of the solution myself independently from your answer, which is not the same, but it's equivalent, of course. Thank you again.

hgulyan Over a year ago

@Lajos Arpad , you're welcome. Please, add you answer too as answer or in your question, so that others can see solution.

Lajos Arpad Over a year ago

It is equivalent to yours, there is no difference between the idea behind my solution and yours, so, basically I would duplicate your answer, as I started to solve the AM/PM problem based on your initial answer. There is no point really to put in my answer, because there are no relevant differences.

|

Collectives™ on Stack Overflow

String to DateTime in SQL Server

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related