out-of-range smalldatetime value error message

[gillianm1]

Hi,

I have the following sql statement:

select CPYEA4, CPPERI from DWCSYPER where '01/01/99'
BETWEEN convert(datetime,CPFDAT) and convert(datetime,CPTDAT)

where CPFDAT and CPTDAT are char datatype. The statement returns a value but also returns the following error message:

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Does anyone know how I can fix / get rid of this error message?
Thanks,
Gillian


[sig][/sig]

 

[THoey]

This flavor of SQL doesn't look familiar, I am of the Oracle type, but I can give a possible reason for the error. Oracle gives a very vague error like that when you try to convert a string like '42-OCT-99' to a date, since 42 is obviously not a valid date.

I don't know how big your table is, but I think you need to go through and look for an invalid date.

If you are going to be comparing these two string fields to date all the time, what is the chance of changing it to a an actual date field? I was always taught that if you have to convert a datatype, you may need to re-think your design.

Hope this helps.
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[gillianm1]

Thanks for the suggestion but the problem is I am importing the date values from another database in the form yyyymmdd with no month day year seperators so I have to import them into a text field or the import fails.
Also the table has hunndreds of thousands of records so looping through it for valid dates wouldn't really suit. Anyhow it's actually failing on valid dates.
Thanks anyhow though,
Gillian [sig][/sig]

 

[MalcolmW]

Assuming that the following state generates the same error message
select convert(datetime,CPFDAT) from DWCSYPER
then there must be some strings that are not correctly formatted for date conversion.
To identify these
select CPFDAT
from DWCSYPER
where ISDATE(CPFDAT) = 0
[sig][/sig]