Bug in script

I thought this had worked but it didn't.

Here is the original post:

This is the script given as response:

SET ExpiresOn = CONVERT(VARCHAR(10), ExpiresOn, 103)
WHERE ISDATE(ExpiresOn) = 1

But that converts to this:   Sep  2 201

It loses the year's last number and also should be in this format:   mm/dd/yyyy

Unfortunately I assigned the points to early. So I am reposting.

----  Original post ----

I currently have a field which is 'ExpiresOn' it is a varchar (25)

 Sometimes user will enter a date and sometimes something else like "NA".
 Problem I had is we added date & time in that field so then it displays in the database as "May 11 2011 12:00AM"

 I need a query that will reformat any entry that is a date time with the format above to the following format:  mm/dd/yyyy, using the example above it would be 05/11/2011

 Help is greatly appreciated.  Example of entries in my DB attached.

 Table name is "Users"
 Field name is: "ExpiresOn"
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>UPDATE Users
SET ExpiresOn = CONVERT(VARCHAR(10), ExpiresOn, 103)
WHERE ISDATE(ExpiresOn) = 1

What's the data type of field: ExpiresOn? Any reason you want to convert the value?

Are you pulling the date field ExpiresOn via ASP? Could it be a formatting issue on your ASP script instead of value issue in MS SQL?
The query is incorrect indeed. I recommend you use the following query:
SET ExpiresOn = CONVERT(VARCHAR(25), CONVERT(DATE, ExpiresOn), 101)
WHERE ISDATE(ExpiresOn) = 1

Open in new window

However, if you did not back up your table before the last update you already have these values in the table that will not be converted. If you have a backup please restore the table that was before you run the previous update.
You can test what the new data will look like after update by running this query:
SELECT ExpiresOn as originalValue, 
    CONVERT(VARCHAR(25), CONVERT(DATE, ExpiresOn), 101) as newValue
    FROM Users
    WHERE ISDATE(ExpiresOn) = 1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
Thank you .. testing ...
AleksAuthor Commented:
Great !! Thank you so much !!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.