How to convert String date into another date format in SQL server?

Question

I have a column which stores date in String format like (11/01/2010,2014-04-08,14-Oct-14).
I want to convert all this type of string dates in datetime/date format like (Dec 16 2014 3:40PM) I am trying like that

update #test set new_CHQ_DATE = CONVERT(varchar(20), CHQ_DATE, 105) where CHQ_DATE like  '__/__/____'
update #test set new_CHQ_DATE = CONVERT(varchar(20), CHQ_DATE, 105) where CHQ_DATE like  '____-__-__'
update #test set new_CHQ_DATE = CONVERT(varchar(20), CHQ_DATE, 105) where CHQ_DATE like  '__-___-__'

Bu

Um, it would be far better for you to store it as an actual DATE type. For one thing, your chosen format doesn't sort nicely, so it'd be useless to add that column to an index. — Clockwork-Muse
– Clockwork-Muse, Commented Dec 17, 2014 at 12:18
Before you convert your column to a varchar, you have to first convert it to a Date or Datetime. Converting a varchar to a varchar doesn't do anything. — Tab Alleman
– Tab Alleman, Commented Dec 17, 2014 at 14:28
You really should us the date(time) datatype for this. Leave the formatting to the front end where it belongs. — Sean Lange
– Sean Lange, Commented Dec 17, 2014 at 14:38

Steve Rowland · Accepted Answer · 2014-12-17 15:41:20Z

2

Your sample seems to work, although it relies on you knowing ahead of time all of the various different ways that people may have entered dates. SQL Server is pretty decent about converting strings into dates, if the string is in basically any NORMAL date recognizable format. This query seems to get more or less all of them (assuming that CHQ_DATE is some kind of VARCHAR and that new_CHQ_DATE is a DATETIME column):

SET DATEFORMAT dmy;
GO
UPDATE #Test
   SET new_CHQ_DATE = CONVERT(VARCHAR(20), CHQ_DATE, 105)
WHERE
   ISDATE(CHQ_DATE) = 1;

--reset DATEFORMAT back to whatever it was

answered Dec 17, 2014 at 15:41

Steve Rowland

365 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert String date into another date format in SQL server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related