Date manipulation for non-datetime type column 1

[wbodger]

I have a column with data like

02/17/2005
Apr  1 2006 12:00AM
Apr  1 2006 12:00AM
Mar  1 1996 12:00AM
Mar  1 1996 12:00AM
Jan  1 1995 12:00AM
12/1998
Jan  1 2003 12:00AM
09/2001
Nov  1 2000 12:00AM
Jan  1 1993 12:00AM
Mar  1 2003 12:00AM
03/2003
06/2004

And they want me to convert it all to the mm/yyyy format. The field is a varchar field. So, they want it to become

02/2005
04/2006
04/2006
03/1996
03/1996
01/1995
12/1998
01/2003
09/2001
03/2000
03/1993
03/2003
03/2003
06/2004

Is there some trick to this that I can't find, or am I going to have to do a lot of updating and replacing?

wb

 

[gmmastros]

Oh... I guess there's a trick you can use.

The is a function called IsDate. This function returns 1 if a string can be converted to a date, otherwise it will return 0.

So... if IsDate is 1, we can convert your string to a DateTime, then Convert it back to a string with a specific format, and then remove the day from it.

To start....

[tt]
Select Case When IsDate(YourDateColumn) = 1
Then Convert(DateTime, YourDateColumn, 101)
Else NULL
End
[/tt]

This will return an actual DateTime data type. If the string data can be converted to a date, it will, otherwise, it will return NULL.

Next, Convert it back to string, but with a specific format. In this case, it's easiest to use format 103 which is dd/mm/yyyy.


[tt]
Select Convert(VarChar(20),
Case When IsDate(YourDateColumn) = 1
Then Convert(DateTime, YourDateColumn, 101)
Else NULL
End, 103)
[/tt]

Next, we can remove the day part, like this


[tt]
Select SubString(
Convert(VarChar(20),
Case When IsDate(YourDateColumn) = 1
Then Convert(DateTime, YourDateColumn, 101)
Else NULL
End, 103), 4, 7)
[/tt]

This will give you the data you are looking for, but only for those dates than can be converted. So the last step is to merge the 2 steps together, which is simple enough because we have NULLs for those strings that cannot be converted.

[tt]
Select Coalesce(
SubString(
Convert(VarChar(20),
Case When IsDate(YourDateColumn) = 1
Then Convert(DateTime, YourDateColumn, 101)
Else NULL
End, 103), 4, 7), YourDateColumn)
[/tt]

Complex, I know, but it should work pretty well.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Well, I grabbed it, inserted my column names and tables etc and it worked great! And your explanation was wonderful. Thank you!

wb