| Author |
Message
|
| venky |
 Posted: Sun Dec 07, 2003 5:33 pm Post subject: Subtracting two dates. |
 |
|
Master
Joined: 08 Jul 2003
Posts: 205
|
Hello All,
Iam trying to Subtract two dates,
(CURRENT_DATE - DATE Temp) DAY;
Where Temp is a variable which holds a Date in the format
yyyy-mm-dd
Can I get the difference in day for such a Condition ?
Please help
Thanks,
Venky
-- |
|
| Back to top |
|
 |
| kirani |
| Posted: Sun Dec 07, 2003 11:25 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Yes you can. Make sure Temp is of DATE data type. Your final result will be in INTERVAL datatype.
_________________
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 |
|
|
| venky |
| Posted: Mon Dec 08, 2003 8:02 am Post subject: Date Manipulation. |
|
|
Master
Joined: 08 Jul 2003
Posts: 205
|
Hello Kirani,
This is my Date format:
DECLARE Temp CHARACTER;
DECLARE Temp1 DATE;
SET Temp = '12/04/2003';
CALL FIX_DATE(Temp);
-- The above function call returns date in 'yyyy/mm/dd' format.
SET Temp1 = CAST (substring(Temp from 1 for 10) AS DATE);
SET OutputRoot.XML.Date.Difference =
(CURRENT_DATE - DATE Temp1 ) DAY;
Will This work, please help.
Thanks,
Venky
-- |
|
| Back to top |
|
|
| kirani |
| Posted: Mon Dec 08, 2003 9:33 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Unfortunately this will not work!
When casting any char date to a DATE datatype, it should be in this format DATE 'CCYY-MM-DD'. So your function FIX_DATE should return the char string DATE '2003-12-04'.
Your final ESQL statement would be:
| Code: |
SET OutputRoot.XML.Date.Difference = (CURRENT_DATE - Temp1 ) DAY;
|
Hope this helps.
_________________
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 |
|
|
| venky |
| Posted: Tue Dec 09, 2003 2:03 pm Post subject: |
|
|
Master
Joined: 08 Jul 2003
Posts: 205
|
Hello Kirani,
I tried to follow the Code you gave but still my code gets a Error,
DECLARE Temp CHARACTER;
DECLARE Temp1 DATE;
SET Temp = '12/04/03';
CALL PROC_FixQADDateFormat(Temp);
SET Temp1 = CAST(Temp AS DATE);
SET OutputRoot.XML.Test.Difference.Date = (CURRENT_DATE - Temp1) DAY;
-- Procedure
CREATE FixDate (INOUT Date_1 CHARACTER)
BEGIN
DECLARE YEAR_1 CHARACTER;
DECLARE MONTH_1 CHARACTER;
DECLARE DAY_1 CHARACTER;
SET Date_1 = TRIM(Date_1);
IF (Date_1 IS NULL OR Date_1 = '') THEN
SET Date_1 = NULL;
ELSE
SET YEAR_1 = '20' || SUBSTRING(Date_1 FROM 7 FOR 2);
SET MONTH_1 = SUBSTRING(Date_1 FROM 1 FOR 2);
SET DAY_1 = SUBSTRING(Date_1 FROM 4 FOR 2);
SET Date_1 = 'DATE ''' || YEAR_1 || '-' || MONTH_1 || '-' || DAY_1 || '' ;
END IF;
END;
The above code gives me a ERROR:
'Error casting from %3 to %4'
Please Help.
Thanks,
Venky. |
|
| Back to top |
|
|
| kirani |
| Posted: Tue Dec 09, 2003 11:23 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Can you post your exceptionlist completely? Also, try making this change in your code,
| Code: |
SET OutputRoot.XML.Test.Difference."Date" = CAST((CURRENT_DATE - Temp1) DAY AS INT);
|
_________________
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 |
|
|
| venky |
| Posted: Thu Dec 11, 2003 2:22 pm Post subject: |
|
|
Master
Joined: 08 Jul 2003
Posts: 205
|
Here is the Exception list.
pls help.
Thanks,
Venky
--
Exception List: (
(0x1000000)RecoverableException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbComputeNode.cpp'
(0x3000000)Line = 453
(0x3000000)Function = 'ImbComputeNode::evaluate'
(0x3000000)Type = 'ComIbmComputeNode'
(0x3000000)Name = '8273e55c-f900-0000-0080-c9e6211fbd11'
(0x3000000)Label = 'CHECK - CREATED TIME.Date'
(0x3000000)Text = 'Caught exception and rethrowing'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2230
(0x1000000)RecoverableException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbRdl/ImbRdlTypeCast.cpp'
(0x3000000)Line = 194
(0x3000000)Function = 'SqlTypeCast::evaluate'
(0x3000000)Type = ''
(0x3000000)Name = ''
(0x3000000)Label = ''
(0x3000000)Text = 'Error casting from %3 to %4'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2521
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '34'
)
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '13'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = 'CHARACTER'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = 'DATE'
)
(0x1000000)RecoverableException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbRdl/ImbRdlValueOperations.cpp'
(0x3000000)Line = 2895
(0x3000000)Function = 'castCharacterToDate'
(0x3000000)Type = 'ComIbmComputeNode'
(0x3000000)Name = '8273e55c-f900-0000-0080-c9e6211fbd11'
(0x3000000)Label = 'CHECK - CREATED TIME.Date'
(0x3000000)Text = 'String '%1' cannot be converted to a date'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2460
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ''DATE '2003-12-04''
) |
|
| Back to top |
|
|
| venky |
| Posted: Sun Dec 14, 2003 7:59 pm Post subject: Subtracting dates, please reply....... |
|
|
Master
Joined: 08 Jul 2003
Posts: 205
|
Hello Kirani,
I have the Exception list as you requested, thanks,
Pls help,
venky |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Dec 15, 2003 5:31 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Counting the quotes, it looks to me like
| Quote: |
| (0x3000000)Text = ''DATE '2003-12-04'' |
is missing a trailing single-quote.
That is, you're trying to convert the string |DATE '2003-12-04| (pipes used as delimiters to avoid confusion) instead of the string |DATE '2003-12-04'|
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| kirani |
| Posted: Mon Dec 15, 2003 4:47 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
As Jeff pointed out, you are missing ' at the end.
You should modify your code following,
| Code: |
DECLARE Temp CHARACTER;
DECLARE Temp1 DATE;
SET Temp = '12/04/03';
CALL FixDate(Temp);
SET Temp1 = CAST(Temp AS DATE);
SET OutputRoot.XML.Test.Difference."Date" = CAST((CURRENT_DATE - Temp1) DAY AS INT);
-- Procedure
CREATE PROCEDURE FixDate(INOUT Date_1 CHARACTER)
BEGIN
DECLARE YEAR_1 CHARACTER;
DECLARE MONTH_1 CHARACTER;
DECLARE DAY_1 CHARACTER;
SET Date_1 = TRIM(Date_1);
IF (Date_1 IS NULL OR Date_1 = '') THEN
SET Date_1 = NULL;
ELSE
SET YEAR_1 = '20' || SUBSTRING(Date_1 FROM 7 FOR 2);
SET MONTH_1 = SUBSTRING(Date_1 FROM 1 FOR 2);
SET DAY_1 = SUBSTRING(Date_1 FROM 4 FOR 2);
SET Date_1 = 'DATE ''' || YEAR_1 || '-' || MONTH_1 || '-' || DAY_1 || ''' ;
END IF;
END;
|
_________________
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 |
|
|
|
|
