format dates correctly to mm/dd/yyyy

[ciarra41]

I have these dates fomatted like these mm/dd/yy to mm/dd/yyyy I tried to convert function but to luck

01/21/98
07/17/96
12/15/04

Need them to look like this"
01/21/1998
07/17/1996
12/15/2004

any help would do

 

[gmmastros]

I suspect you are storing your data in a varchar column, which is unfortunately because it is entirely possible to store bad dates since it is just a string.

In order to do this, you could first convert to date time and then convert back to string. To convert to datetime, you should use the style argument to ensure that you are using mm/dd/yy and then use the style argument to ensure you have mm/dd/yyyy.

Like this:

Select  Convert(VarChar(10), Convert(DateTime, YourColumnNameHere, 1), 101)
From	YourTableNameHere

If you get a conversion error when you run the code above, it is an indication that you have invalid dates in your table.

-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