Datetime conversion problem

[dcedwa3]

I'm having a problem with converting a DOB column from nvarchar to a datetime format. I thought the easiest way to do this would be to create another column in the table "DOB2" and run [update table set DOB2=Convert(datetime, DOB)]. This fails with the following message:

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting datetime from character string.

This is SQL Server 2005, running on a Windows 2003 server. Any help, suggestions, or better ways to do this would be greatly appreciated.

[iburyak]

It looks like you have some garbage data.
Select all from your table and order by DOB you might be able to visualize and correct it.

[dcedwa3]

Thanks iburyak. I should have mentioned that this table has over 250 million records so I probably won't be able to select all and figure out if there are problems in the DOB column, right?

[iburyak]

Try to do this

select Top 100 * from table order by DOB
or
select Top 100 * from table order by DOB desc

This way you would be able to see something abnormal - it is usually at the end or at the beginning of your table and if you have index over this column it should be no problem.

[iburyak]

You can try this:

[PHP]--List all valid date characters in brackets separated by comma.

select Top 100 * from table_name where DOB not like '%[0-9,-,\,:]%'[/PHP]

[dcedwa3]

OK, I ran your second option:
[select Top 100 *
from criminal
where DOB not like '%[0-9,-,\,:]%']

And it returned the first 100 rows but the DOB column is blank, which is understandable, some DOB's aren't given. Should these be Null? and if so how do I update or correct them?

Thank you for the help.

[iburyak]

Good that it helped :) because after I had a second look at the query I wasn't sure if it will work. The thing is this query works in cases where you don't have any numbers or -,\,: but in cases where you have mixed data like “aaaaaa56:00” it wouldn't work. But still try to fix what you see first.

1. Count how many records like this you have in your table.
[PHP]select count(*)
from criminal
where DOB not like '%[0-9,-,\,:]%'[/PHP]

2. Check if they are the problem records by trying to convert them to datetime because I suspect server doesn’t have a problem converting null or empty string to a date. It would be valid date 1900-01-01 00:00:00.000. You have to search for something else in your data that is totally unrecognizable. To test if conversion go smoothly for records like this do following
[PHP]select top 100 convert(datetime, DOB)
from criminal
where DOB not like '%[0-9,-,\,:]%'[/PHP]

3. Try to search for letters in this column.
[PHP]select Top 100 *
from criminal
where DOB like '%[A-Z,a-z]%'[/PHP]

4. Try to do following search. Not sure how will it work on your server it different from version to version.

[PHP]select top 100 *
from criminal
where DOB not like '[^0-9,-,\,:]%' and isnull(DOB,’’) <> ‘’[/PHP]

^ sign means negative, meaning everything that is not. Also I removed nulls and empty strings because we already know they are possibly there and want to see the rest.
Good luck.

[dcedwa3]

iburyak,

I just wanted to say thanks. I finally got a chance to use what you provided and although this was a long process, this was exactly what I needed. Thanks for all of the help and direction, I appreciate it.

[iburyak]

You are welcome... :)