Date Math with YYYYMMDD Fields

[MFisher]

I can get # of days between Current Date and YYYYMMDD date (packed 8,0) with:

SELECT  Days(Current_Date) - Days(My_YMD_Field concat '000000')
FROM MyTable

But get an error if I try to use that in a WHERE clause.

SELECT  Days(Current_Date) - Days(My_YMD_Field concat '000000')
FROM MyTable
WHERE Days(Current_Date) - Days(My_YMD_Field concat '000000') < 10  (also tried '10')

I tried to cast the Number of days result as INT, but also fails

where CAST(Days(Current_Date) - Days(My_YMD_Field concat '000000') as INT) < 10

Suggestions ?

Date field is Packed 8,0

[B.Hauser]

hmmm ...
The following query works for me:

With x (Datum) as (Values(Dec(20220201, 8, 0)), (Dec(20211201, 8, 0)))
Select * from x
Where Days(Current_Date) - Days(Datum concat '000000') < 100;

[Scott M]

You don't mention what error you get, could My_YMD_Field contain invalid data values? Depending on where you are doing this the Select may work because of paging while the Where has to evaluate every record. I would do this by determining the date you want to compare to in YYYYMMDD format as a numeric value (Current_Date - 10 days essentially) and then using that value in the Where clause.

[MFisher]

You don't mention what error you get, could My_YMD_Field contain invalid data values? Depending on where you are doing this the Select may work because of paging while the Where has to evaluate every record. I would do this by determining the date you want to compare to in YYYYMMDD format as a numeric value (Current_Date - 10 days essentially) and then using that value in the Where clause.

I think you are right. Although my limited result set had valid dates, some other records have 000000 .
If I add
WHERE My_YMD_Field > 19000101

I think it will work