469,325 Members | 1,645 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,325 developers. It's quick & easy.

convert current date to integer as in YYYYMMDD

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?
Apr 10 '07 #1
3 76169
Snib
24
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.sysdummy1
;

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_column, 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
Apr 11 '07 #2
v0rtex
1
A simpler method might be:

select int(replace(char(current date - 1 day, ISO),'-',''))
from sysibm/sysdummy1
May 17 '07 #3
Thank you both.
Will try the suggestion and let you know the status.

Regards,
Akhila
Mar 20 '15 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Chris | last post: by
6 posts views Thread by vijayk | last post: by
5 posts views Thread by Extremest | last post: by
6 posts views Thread by phforum | last post: by
5 posts views Thread by Pontifex | last post: by
4 posts views Thread by Steve Rainbird | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.