convert current date to integer as in YYYYMMDD

[seanw89]

I have a bit trouble in converting CURRENT DATE into a integer type as YYYYMMDD.

The following script is trying to find a day matching a date column with integer type with yesterday (CURRENT DATE - 1 DAY).

[column name of integer type] = YEAR(CURRENT DATE - 1 DAY) * 10000 + MONTH(CURRENT DATE - 1 DAY) * 100 + DAY(CURRENT DATE - 1 DAY)

It doesnt give the correct result. Actually the calculation result from right side of the equation doesnt match with any values in the column. Can anyone help me figure out what's wrong with the above statement?

[Snib]

I have just tried you SQL with the following:

select YEAR(CURRENT DATE - 1 DAY) * 10000 + MONTH(CURRENT DATE - 1 DAY)
* 100 + DAY(CURRENT DATE - 1 DAY)
from sysibm.sysdummy 1
;

and got the result:

20070410

which all seems to be fine to me!

What I suggest you do is try running you SQL but type in the integer value you are looking for to verify that the data in the column it is matching against contains what you expect.

Alternatively you could run something like this:

select Integer_date_co lumn, count(*)
from your_table
group by Integrate_date_ column;

This will give you a count of how frequently each integer date value occurs on your table. Then try putting one of these integer values into you SQL to verify it is selecting correctly.

Regards

Snib

[v0rtex]

A simpler method might be:

select int(replace(cha r(current date - 1 day, ISO),'-',''))
from sysibm/sysdummy1

[AkhilaReddy]

Thank you both.
Will try the suggestion and let you know the status.

Regards,
Akhila