Year 2001 Problem 1

[ninatodd]

Hello:

The following works just fine (and has worked just fine) until today's receipts. The receipts dated today are not found.

select * from t_receipt_master
where rtrim(convert(char,receipt_date,101)) >= rtrim(convert(char,(convert(datetime,'11/01/00')),101))

If I turn the query around and look for :

select * from t_receipt_master
where rtrim(convert(char,receipt_date,101)) <= rtrim(convert(char,(convert(datetime,'01/02/01')),101))

It finds today's receipt date.

Does anyone have any suggestions on why the first part is failing? Any help would be appreciated, the query has been working fine until today.

Thank you in advance..

 

[fluteplr]

without working through your convoluted code, could I suggest you use an unambiguous date format such as
'2000-12-31' you can skip all the cast and other junk
just do a compare like
mydatefield <='2000-12-31'

if you need to account for times also then
mydatefield <='2000-12-31 23:59:59' sql server will convert the character date in that format automatically without a cast or convert

 

[MarkSweetland]

When you convert the reciept_date to a char type in format 101 you drop the hours, minutes, and seconds of the datetype and then try to compare to a converted datetime that does contain them.

To find your 2001 reciept date, change the WHERE clause to this:

WHERE

cast(rtrim(convert(char,reciept_date,101))as datetime)

 >= rtrim(convert(char,(convert(datetime, '11/01/00')),101))

Mark

 

[ninatodd]

Thanks for the tips - We ended up using format 111 instead of 101 and it worked fine.