Link to home
Start Free TrialLog in
Avatar of aaran
aaran

asked on

Convert a date stored in a varchar field (dd/mm/yyyy to yyyy-mm-dd).

I have a field in my Table called "DetailValue". This field is used to store a variety of date (including dates, text etc).

I need to find all values in this table that are dates (in the form of dd/mm/yyyy), and then convert them to the form yyyy-mm-dd.

How can I do this?????
Avatar of hongjun
hongjun
Flag of Singapore image

Welcome :)
ASKER CERTIFIED SOLUTION
Avatar of KarinLoos
KarinLoos

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Scott Pletcher
If the column contains just the date:

UPDATE tableName
SET DetailValue = CONVERT(VARCHAR(10), DetailValue, 120)
WHERE ISDATE(DetailValue) = 1

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of aaran
aaran

ASKER

hehe...feel the need to explain myself now.

The reason for various datatypes all being stored in one field, is because these values are the answers to dynamically created form fields. OUR customers can define their own fields (date, text, decimal, currency, radio etc) and THEIR customers fill in the answers. This solutions was the most applicable one, albeit not necessarily the most acceptable! LOL
Avatar of aaran

ASKER

Also, I have now found my own solution, using an ASP.NET standalone app that gets a list of all values that are 10 characters in length, of which the 3rd and 6th are "/". It then substrings the dd, mm and yyyy parts and reverses them accordingly.

Many thanks for your suggestions...

Aaran
what about when they are ddmmyyyy  in a varchar column and not a datetime column, how would you convert then?