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?????
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?????
Microsoft SQL Server
Last Comment
Nathan Riley
Welcome :)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
If the column contains just the date:
UPDATE tableName
SET DetailValue = CONVERT(VARCHAR(10), DetailValue, 120)
WHERE ISDATE(DetailValue) = 1
UPDATE tableName
SET DetailValue = CONVERT(VARCHAR(10), DetailValue, 120)
WHERE ISDATE(DetailValue) = 1
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
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
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?