| Author |
Message
|
| kayhansefat |
 Posted: Mon Sep 24, 2007 5:07 am Post subject: Date - Date = Interval in Days Problem |
 |
|
Acolyte
Joined: 18 Oct 2006
Posts: 65
|
Hi,
I am trying:
| Code: |
DECLARE output1 INTERVAL;
DECLARE hello DATE CURRENT_DATE;
DECLARE testDate DATE '2004-09-13';
SET hello = hello + INTERVAL '5' DAY;
SET output1 = (hello - testDate) DAY; |
However the variable 'output1' never seems to be populated with a value. the variable 'hello' correctly gets incremented by 5 days.
Any ideas? WMB v6 on Linux x86
Thanks. |
|
| Back to top |
|
 |
| vk |
| Posted: Mon Sep 24, 2007 6:04 am Post subject: |
|
|
Partisan
Joined: 20 Sep 2005
Posts: 302
Location: Houston
|
Did you try turning on the trace to see whether output1 is populated with any value? What is happening when the line SET output1 = (hello - testDate) DAY; is executed?
Your code looks fine. output1 should be populated with something like INTERVAL 'XXXX' DAY, where XXXX is the difference in days between the 2 date values.
Regards,
VK. |
|
| Back to top |
|
|
| kayhansefat |
| Posted: Mon Sep 24, 2007 6:38 am Post subject: |
|
|
Acolyte
Joined: 18 Oct 2006
Posts: 65
|
Thanks vk.
I have been debugging the flow with the RAC. When that line is run, 'output1' is still NULL - no change. |
|
| Back to top |
|
|
| vk |
| Posted: Mon Sep 24, 2007 7:23 am Post subject: |
|
|
Partisan
Joined: 20 Sep 2005
Posts: 302
Location: Houston
|
I ran your code with mqsichangetrace. output1 variable is getting populated with the value INTERVAL '1111' DAY
Regards,
VK. |
|
| Back to top |
|
|
| kayhansefat |
| Posted: Tue Sep 25, 2007 12:31 am Post subject: |
|
|
Acolyte
Joined: 18 Oct 2006
Posts: 65
|
| Yes VK you are indeed right, doing a trace on it I can see that it is set correctly, thanks. The variable's value was not set in the Variable List in the Debug Perspective though. |
|
| Back to top |
|
|
| mqqmgr |
| Posted: Tue Sep 25, 2007 3:53 am Post subject: |
|
|
Novice
Joined: 11 Sep 2006
Posts: 14
|
Hi,
Can you please try this one.
DECLARE output1 INTEGER;
DECLARE hello DATE CURRENT_DATE;
DECLARE testDate DATE '2004-09-13';
SET hello = hello + INTERVAL '5' DAY;
SET output1 = CAST((hello - testDate) AS INTEGER);
Please let me know if it is working |
|
| Back to top |
|
|
| kayhansefat |
| Posted: Tue Sep 25, 2007 4:55 am Post subject: |
|
|
Acolyte
Joined: 18 Oct 2006
Posts: 65
|
Yes that is what I have ended up doing and it works. The INTERVAL is being correctly set (and hence the integer) but the INTERVAL's value was not being shown in the debug variable list!
Thanks for all your help. |
|
| Back to top |
|
|
|
|
