multiple Date conversions

Question

multiple Date conversions

hegdesuchi

Hall of Fame

Points: 3047
More actions
November 25, 2015 at 1:42 pm

#322552

Hi,
I have a scenario where i have to covnert date into particular format. There are multiple date formats that needs to be converted.
For Example:Actual Data is
ID Date
1 07/14/1999
2 26-SEP-2009
3 Dec 15 1940 12:00AM
4 01/14/2015
Date field here is varchar(50). I want the below result and that should in Date format.
ID Date
1 07/14/1999
2 09/26/2009
3 12/15/1940
4 01/14/2015
Can anyone of you help me in getting these formats? There are two different date formats that needs to be converted to mm/dd/yyyy format. this will be included in the view.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

hegdesuchi (11/25/2015)
Hi,
I have a scenario where i have to covnert date into particular format. There are multiple date formats that needs to be converted.
For Example:Actual Data is
ID Date
1 07/14/1999
2 26-SEP-2009
3 Dec 15 1940 12:00AM
4 01/14/2015
Date field here is varchar(50). I want the below result and that should in Date format.
ID Date
1 07/14/1999
2 09/26/2009
3 12/15/1940
4 01/14/2015
Can anyone of you help me in getting these formats? There are two different date formats that needs to be converted to mm/dd/yyyy format. this will be included in the view.

All the examples you provided are valid dates, so you should be able to convert them to your desired format by first converting them to DATES and then to the VARCHAR format with this simple conversion:

CONVERT(VARCHAR(10), CAST([Date] AS DATE), 101))

You might want to add a little something to prevent errors if a bad date is encountered. For example:

CASE WHEN ISDATE([Date]) = 1 THEN CONVERT(VARCHAR(10), CAST([Date] AS DATE), 101)) ELSE NULL END

Replace NULL with your choice for bad dates.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 2

thank you very much.. this indeed solved the error. I was trying with Casting as Date and that was throwing the error for me.