| Author |
Message
|
| LH33 |
 Posted: Thu Nov 21, 2002 11:25 am Post subject: Date and Time Manipulation |
 |
|
Master
Joined: 21 Nov 2002
Posts: 200
|
I have a need to add 48 hours to both a date and a time field that will be passed to me via XML. Can anyone help me with how to do this.
For example - I will be sent a CREATE DATE of say 20021114 I need to add 48 hours to this to form an END BEFORE DATE field.
Thanks!! |
|
| Back to top |
|
 |
| kirani |
| Posted: Thu Nov 21, 2002 7:39 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Try something like this,
| Code: |
DECLARE CURDT DATE;
SET CURDT = CURRENT_DATE;
DECLARE NEWDT DATE;
SET NEWDT = CURDT + INTERVAL '48' HOUR;
|
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| LH33 |
| Posted: Fri Nov 22, 2002 3:28 am Post subject: |
|
|
Master
Joined: 21 Nov 2002
Posts: 200
|
|
| Back to top |
|
|
| vmurdesh |
| Posted: Wed Nov 19, 2003 4:58 pm Post subject: INTERVAL function does not work in v2.1 CSD02 ? |
|
|
Novice
Joined: 09 May 2002
Posts: 18
|
Hi,
I tried the following ESQL with the INTERVAL function and it does not work in v2.1 CSD02 on NT ?
DECLARE tempCovExpDateSQL DATE;
SET tempCovExpDateSQL = DATE '2003-08-31' + INTERVAL '3' MONTH;
The same statement works when DATE '2003-10-31'
Is this a bug ? Do we need CSD05?
Thanks in advance.. |
|
| Back to top |
|
|
| LH33 |
| Posted: Thu Nov 20, 2003 6:16 am Post subject: |
|
|
Master
Joined: 21 Nov 2002
Posts: 200
|
VMURDESH,
I used the code Kiran suggested above and it works fine. I am on version 2.1 CSD05 on NT.
Thanks, Lisa |
|
| Back to top |
|
|
| vmurdesh |
| Posted: Thu Nov 20, 2003 10:01 am Post subject: So is this a bug in CSD02 ? |
|
|
Novice
Joined: 09 May 2002
Posts: 18
|
Thanks Lisa....
So is this a bug in CSD02 ? does this mean we would have to do an upgrade for this to CSD05 fron CSD02 ?
Thanks again.. |
|
| Back to top |
|
|
| LH33 |
| Posted: Thu Nov 20, 2003 10:06 am Post subject: |
|
|
Master
Joined: 21 Nov 2002
Posts: 200
|
VMURDESH,
I'm not sure if the error is in the CSD02 since I always had CSD05. Since we are both on the same version of Control Center, I would upgrade to CSD05.
Thanks, Lisa |
|
| Back to top |
|
|
| kirani |
| Posted: Sat Nov 22, 2003 10:55 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
vmurdesh,
I'd recommend that you open a PMR for this problem.
If you want to upgrade to a higher CSD then please go thru the readme files for CSD3, 4 and 5 before installing it.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| vmurdesh |
| Posted: Tue Nov 25, 2003 12:31 pm Post subject: proplem persists even with CSD 5 |
|
|
Novice
Joined: 09 May 2002
Posts: 18
|
Hi Kiran,
Thanks for your reply.
I installed CSD 5 on NT and tried the foll ESQL in a simple compute node:
DECLARE tempCovExpDateSQL DATE;
DECLARE covExpDateSQL DATE;
SET covExpDateSQL = DATE '2003-08-31';
SET Environment.Variables.MYcovExpDateSQL = covExpDateSQL;
SET tempCovExpDateSQL = DATE '2003-08-31' + INTERVAL '3' MONTH;
SET Environment.Variables.MYtempCovExpDateSQL = tempCovExpDateSQL;
(it works with SET tempCovExpDateSQL = DATE '2003-10-31' + INTERVAL '3' MONTH;)
Thanks.
Vinayak
This is what happens on the red statement on NT.
==================
Trace Contents
==================
(
(0x3000000)MYcovExpDateSQL = DATE '2003-08-31'
)
(
(0x1000000)RecoverableException = (
(0x3000000)File = 'F:\build\S210_P\src\DataFlowEngine\ImbDataFlowNode.cpp'
(0x3000000)Line = 536
(0x3000000)Function = 'ImbDataFlowNode::createExceptionList'
(0x3000000)Type = 'ComIbmComputeNode'
(0x3000000)Name = '10dd2023-f200-0000-0080-ea4d9762a08c'
(0x3000000)Label = 'SyntaxTest.SyntaxTest'
(0x3000000)Text = 'Node throwing exception'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2230
(0x1000000)RecoverableException = (
(0x3000000)File = 'F:\build\S210_P\src\DataFlowEngine\ImbRdl\ImbRdlBinaryExpression.cpp'
(0x3000000)Line = 297
(0x3000000)Function = 'SqlBinaryPlus::evaluate'
(0x3000000)Type = 'ComIbmComputeNode'
(0x3000000)Name = '10dd2023-f200-0000-0080-ea4d9762a08c'
(0x3000000)Label = 'SyntaxTest.SyntaxTest'
(0x3000000)Text = 'error evaluating expression'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2439
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '12'
)
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '43'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = 'DATE '2003-08-31' + INTERVAL INTERVAL '3' MONTH'
)
(0x1000000)RecoverableException = (
(0x3000000)File = 'F:\build\S210_P\src\CommonServices\ImbDateTime.cpp'
(0x3000000)Line = 3026
(0x3000000)Function = 'ImbDate::operator+='
(0x3000000)Type = ''
(0x3000000)Name = ''
(0x3000000)Label = ''
(0x3000000)Text = 'Bad date'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2327
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '2003'
)
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '11'
)
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '31'
)
)
)
)
) |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Nov 26, 2003 3:38 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
DATE '2003-08-31' + INTERVAL '3' MONTH;
This will give you the Date '2003-11-31' which is invalid as November only has 30 days. I will check whether we should actually be rounding this, but I don't think so...
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| vmurdesh |
| Posted: Wed Nov 26, 2003 8:39 am Post subject: i think thats a bug |
|
|
Novice
Joined: 09 May 2002
Posts: 18
|
thanks mgk,
i know it calculates it to 2003-11-31 but when the date is a variable this would imply that you have to check the input date before using the INTERVAL statement which makes it pointless.
I have opened a PMR with IBM for this.
for example:
adding 3 months to '2003-08-31' should be '2003-11-30' ? (it seems to be calculating 2003-11-31 and blowing up)
adding 90 days to '2003-08-31' will be '2003-11-29'..
This is how we would code it in our java or cobol apps.
Thanks.
Vinayak. |
|
| Back to top |
|
|
|
|
