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:

Expand|Select|Wrap|Line Numbers

 ... 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

Quote:

Originally Posted by 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:

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

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

Quote:

Originally Posted by 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:

Expand|Select|Wrap|Line Numbers

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

Quote:

Originally Posted by PlayHard

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.

Quote:

Originally Posted by PlayHard

Have a great Thanksgiving!

Thank you, the same to you.

Date issue on interger field

re: Date issue on interger field

re: Date issue on interger field

re: Date issue on interger field

re: Date issue on interger field

re: Date issue on interger field

/bytes/development

/bytes/it

/bytes/about