Date issue on interger field

[PlayHard]

I currently have a interger field with this format (YYYYMMDD) Example 20071118

I want to automate a weekly report Sun - Sat, therefore do not want to hard code the date. I want to say (Current Date - 7 days).

I have tried multiple things but they do not work.

Please help!

Thank YOU

[docdiesel]

Hi,

"Current Date - 7 days" only works on date or timestamp columns. If you stick to your integer field, you need to transform this date value to integer like:

... where
  my_int_date >=( year(Current Date - 7 days) * 10000
               + month(Current Date - 7 days) * 100
               +   day(Current Date - 7 days) * 1 )

Regards, Bernd

[PlayHard]

Hi,

"Current Date - 7 days" only works on date or timestamp columns. If you stick to your integer field, you need to transform this date value to integer like:

... where
  my_int_date >=( year(Current Date - 7 days) * 10000
               + month(Current Date - 7 days) * 100
               +   day(Current Date - 7 days) * 1 )

Regards, Bernd

Wow!! It works.

Please explain how it works.

For current date I am using Nov 20. So if I use the formula you gave I get

Year(20-7)*1000=13000
Month(20-7)*100=1300
Day(20-7)*1=13

13000+1300+13=14313 (How does 14313 equal Nov 13 ?

Thanks again. Your a life Saver!

[docdiesel]

Hi,

glad to hear you're saved. But your calculation is slightly wrong. Assumed today is the 20th of november, then current_date() would give '2007-11-20' as result. Put this into your calculation to see what happens:

-> current_date() - 7 days 
=    '2007-11-20' - 7 days
=    '2007-11-13'
 
 Year( '2007-11-13' ) * 10000 = 2007 * 10000 = 20070000
Month( '2007-11-13' ) *   100 =   11 *   100 =     1100
  Day( '2007-11-13' ) *     1 =   13 *     1 =       13
-------------------------------------------------------
                                         Sum = 20071113
 

Regards, Bernd

[PlayHard]

Hi,

glad to hear you're saved. But your calculation is slightly wrong. Assumed today is the 20th of november, then current_date() would give '2007-11-20' as result. Put this into your calculation to see what happens:

-> current_date() - 7 days 
=    '2007-11-20' - 7 days
=    '2007-11-13'
 
 Year( '2007-11-13' ) * 10000 = 2007 * 10000 = 20070000
Month( '2007-11-13' ) *   100 =   11 *   100 =     1100
  Day( '2007-11-13' ) *     1 =   13 *     1 =       13
-------------------------------------------------------
                                         Sum = 20071113
 

Regards, Bernd

That is really cool! Awesome.

My only concern is when I run the report on January 6, 2008 and go back 7 days, is the system smart enough to know I want December 30 to 31st and January 1st to 5th. ?

Have a great Thanksgiving!

[docdiesel]

My only concern is when I run the report on January 6, 2008 and go back 7 days, is the system smart enough to know I want December 30 to 31st and January 1st to 5th. ?

Yes, it is. That's why there's the "+- x days/minutes/etc" possibility. After all, inside DB2 date, time and datestamp fields are nothing else than integers. But just not the way you seem to need them.

Have a great Thanksgiving!

Thank you, the same to you.