SQL Server Performance Forum – Threads Archive

Newby Datetime question

I have a varchar filed that I want to conver permanently to a Datetime field.
The data in the field looks like this 01/01/2002 and it ranges from 01/01/1775 to today and it also has some nulls How can I accomplish this? Thanks for your help.

Hi ya, datetime allows you to store dates from 1-Jan-1753 to 31-Dec-9999 so you fall within this range I’d do the following steps - rename the existing column using sp_rename ‘table.col’, ‘colnew' - alter the table to add the new coldate column - use update table set coldate = convert( datetime, colnew ) - check the data is correct - drop the colnew column best to test this on a copy of your database… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

Thanks for your reply Twan, I tried what you suggested and I got an error that read: "conversion from char datatype to datetime resulted in an out of range date
Statement terminated" What else can I try? Please be very specific I’m very new to TSQL.
Thanks.
Rene

Try to see date format in SQL versus date format in TSQL Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

What this means is that not all your dates are in the same format. Since the field was varchar, unless the front end application enforced it, users could enter the date anyway that they wanted.
For example, mm/dd/yyyy or dd/mm/yyyy or ddmmmmyyyy or the choices are endless Depending on how many rows you have, you could eyeball and see if you can find the culprit or you easier, try a query like this
select [identifyingcolumn], [characterdatecolumn]
from table
where isdate([characterdatecolumn]) = 0 the isdate function will return a 1 if a field is a valid date format. Even when you get past this, keep in mind that a text field 01/02/2003 could be either Jan 2, 2003 or Feb 1, 2003 depending on who entered it. Unless you know for sure which it is, you may want to do more checking. Chris

Thanks for all your replies. I think I found the problem, the column contain some invalid dates such as 01/01/0100. I made the necesary changes and I’m now running the update statement that was suggested earlier. I have over 15 million records on the table, it’s been running for almost an hour now and have not gotten any error messages so far. I hope that it works. Thanks again for all your help.
Rgds. Rene

]]>