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"