Convert Char To Date Format 1

[sa0309]

I have columns with the following date format:

5-FEB-09
5-MAY-10
6-JUL-09
6-OCT-08


I want to convert this to mmddyyy format.

how do i do this?

Thank you.

 

[gmmastros]

Convert first to date and then back to varchar with the correct style. Like this:

Select Convert(VarChar(10), Convert(DateTime, YourColumnName), 101)
From   YourTableName

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sa0309]

I get an error: Syntax error converting datetime from character string.(22007,241)

Thanks

 

[gmmastros]

The code works for the sample data you show. You must have some data that does not convert properly. To find it...

Select *
From   YourTableName
Where  IsDate(YourColumnName) = 0

You can ignore invalid dates and return NULL like this...

Select Convert(VarChar(10), Convert(DateTime, Case When IsDate(YourColumnName) = 1 Then YourColumnName Else NULL End), 101)
From   YourTableName

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sa0309]

Yes...I did data not converting properly. Your 1st suggestion worked perfectly.

Thanks for the help.

 

[djj55]

This thread brings up a problem I have in that I want to output mmddyyyy not mm/dd/yyyy and end up using REPLACE as no style exists. Luckily I more often then not need Style 112 for output.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!