out-of-range smalldatetime value.

[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]

 

[foxdev]

You should specify four-digit years in your literals, as in:
'01/01/1999'

My guess is that '/99' was interpreted as the year '/0099', which indeed is out of range for a SmallDateTime value. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[Guest_imported]

Thanks Robert but I tried that and it doesn't work.

 

[foxdev]

Can you give us some examples of the values in your CPFDAT and CPTDAT columns?

Have you looked at the values in all of the columns to ensure they are proper? If one row's value or formatting is out of whack, it could cause that message. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[karluk]

I'm puzzled why the error message specifically says that the error is on conversion to smalldatetime, rather than datetime. The two convert functions are to datetime, and a quick test indicates that the '01/01/99' is also interpreted as a datetime. I don't see any place in your SQL where smalldatetime is being used. [sig][/sig]

 

[gillianm1]

Here are some examples:

CPFDAT CPTDAT
27/12/1998 23/01/1999
24/01/1999 20/02/1999
21/02/1999 27/03/1999
28/03/1999 24/04/1999
25/04/1999 22/05/1999
23/05/1999 26/06/1999

I've just checked to see if all of the values are valid and they're not. I have afew 0 values which seem to be causing the problem. I'm going to replace them with something else.
Thanks for your help,
Gillian [sig][/sig]

 

[foxdev]

I think if even one row has a wacky value, it will cause that error message.

Just out of curiosity, Gillian, why use the Character data type for your columns, rather than DT or SDT? [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]