Converting a Text Datatype field into a DateTime.

Question

I'm using SQL Server 2000 and my ERP vendor has used a Text datatype field to hold Dates.

The Dates look like:

6/21/2001

However, some of them are blank.

I've tried using something like:

select coalesce(nullif(fdelivery, ''), '1/1/1900') FROM sorels 

to remove the blanks to no avail.

I've also tried:

case 
        when ltrim(rtrim(SOR.fdelivery)) = ''

to remove blanks to no avail.

Even if I can substitute the blanks with something that looks like a date, how can I convert or cast the field to be datetime (from Text) so that my reporting program knows that it is in fact a Date field and not Text.

Answer 1

It's been a while since I used SQL Server 2000, but if the ISDATE function is available, you could use it like this:

SELECT CAST((CASE WHEN ISDATE(CAST(fdelivery as varchar(20)) = 0 THEN '1-1-1900' ELSE CAST(fdelivery as varchar(20)) END) AS datetime) 
FROM sorels

Answer 2

Ah, I found a method:

select 
Case
when isdate(SUBSTRING(fdelivery, 1,11)) = 1
then cast(SUBSTRING(fdelivery, 1,11) as datetime)
else cast('1/1/1900' as datetime)
end
as DateConvert
from sorels

This will work. It substrings out the date "portion" of the text field which I guess implicitly converts it to a Char datatype and then I can cast that as a datetime.