Unanswered: How to convert datetime from varchar to datetime
How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.
following are the sample data that is theer in the older table for the date.
there is no uniformity of the data that is stored currently.
Will convert but throw up an error.
This format cannot be trusted!
Test each format of your code thoroughly!
WHEN LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' THEN
WHEN LIKE '[0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] THEN
WHEN LIKE ...
END As [converted]
QA returns dates as YYYY-MM-DD -- which is neither British or American, it is just a universal date format. I don't think you can easily change this in QA. It is definitely a short-coming in that application, it would be nice to have more control over how different data types are formatted and does lead to lots of confusion over presentation-versus-data when people feel forced to do it in T-SQL to see things the way they want in QA.
In SQL Server Management Studio it will use your computer's regional date time settings.
Originally Posted by georgev
For your example of 12/12/2003 it will happily accept 12/31/2003 and will fail on 31/12/2003.