By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,333 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,333 IT Pros & Developers. It's quick & easy.

Date issue on interger field

P: 7
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
Nov 19 '07 #1
Share this Question
Share on Google+
5 Replies


docdiesel
Expert 100+
P: 297
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:

Expand|Select|Wrap|Line Numbers
  1. ... where
  2.   my_int_date >=( year(Current Date - 7 days) * 10000
  3.                + month(Current Date - 7 days) * 100
  4.                +   day(Current Date - 7 days) * 1 )
Regards, Bernd
Nov 20 '07 #2

P: 7
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:

Expand|Select|Wrap|Line Numbers
  1. ... where
  2.   my_int_date >=( year(Current Date - 7 days) * 10000
  3.                + month(Current Date - 7 days) * 100
  4.                +   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!
Nov 20 '07 #3

docdiesel
Expert 100+
P: 297
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:

Expand|Select|Wrap|Line Numbers
  1. -> current_date() - 7 days 
  2. =    '2007-11-20' - 7 days
  3. =    '2007-11-13'
  4.  
  5.  Year( '2007-11-13' ) * 10000 = 2007 * 10000 = 20070000
  6. Month( '2007-11-13' ) *   100 =   11 *   100 =     1100
  7.   Day( '2007-11-13' ) *     1 =   13 *     1 =       13
  8. -------------------------------------------------------
  9.                                          Sum = 20071113
  10.  
Regards, Bernd
Nov 21 '07 #4

P: 7
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:

Expand|Select|Wrap|Line Numbers
  1. -> current_date() - 7 days 
  2. =    '2007-11-20' - 7 days
  3. =    '2007-11-13'
  4.  
  5.  Year( '2007-11-13' ) * 10000 = 2007 * 10000 = 20070000
  6. Month( '2007-11-13' ) *   100 =   11 *   100 =     1100
  7.   Day( '2007-11-13' ) *     1 =   13 *     1 =       13
  8. -------------------------------------------------------
  9.                                          Sum = 20071113
  10.  
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!
Nov 21 '07 #5

docdiesel
Expert 100+
P: 297
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.
Nov 22 '07 #6

Post your reply

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