Author |
Message
|
nukalas2010 |
Posted: Tue Mar 12, 2013 1:36 am Post subject: Check the Date value |
|
|
 Master
Joined: 04 Oct 2010 Posts: 220 Location: Somewhere in the World....
|
Hi Dears,
When Incoming date value contains 2013-03-11, the below ESQL code is fine.
Code: |
DECLARE dDate DATE TRIM(FIELDVALUE(InputRoot.XMLNSC.Request.Date));
IF (dDate >= CURRENT_DATE) THEN
SET Environment.Variables.Status = 1000;
ELSE
SET Environment.Variables.Status = 1001;
END IF; |
But, In some cases Incoming date value will be 0000-00-00, Then the above ESQL code will not work.
So, I have done changes as below.
Code: |
DECLARE cDate CHARACTER TRIM(FIELDVALUE(InputRoot.XMLNSC.Request.Date));
DECLARE dDate DATE;
IF CONTAINS(cDate, '0000-00-00') THEN
SET dDate = CURRENT_DATE - CAST(1 AS INTERVAL DAY);
ELSE
SET dDate = TRIM(FIELDVALUE(InputRoot.XMLNSC.Request.Date));
END IF;
IF (dDate >= CURRENT_DATE) THEN
SET Environment.Variables.Status = 1000;
ELSE
SET Environment.Variables.Status = 1001;
END IF; |
Is there any better way to achive this ??  |
|
Back to top |
|
 |
fjb_saper |
Posted: Tue Mar 12, 2013 3:03 am Post subject: |
|
|
 Grand High Poobah
Joined: 18 Nov 2003 Posts: 20756 Location: LI,NY
|
A date is a DATE is a date. Your input is not a date. Did you associate the null value for the field with that value? (0000-00-00)  _________________ MQ & Broker admin |
|
Back to top |
|
 |
nukalas2010 |
Posted: Tue Mar 12, 2013 4:03 am Post subject: |
|
|
 Master
Joined: 04 Oct 2010 Posts: 220 Location: Somewhere in the World....
|
fjb_saper wrote: |
Your input is not a date. |
Yes, My Input is not a date. But to check whether the Requestdate is greater than or equal to the CURRENT_DATE it should be in Date format. right?? Sorry if I miss something here..  |
|
Back to top |
|
 |
mqjeff |
Posted: Tue Mar 12, 2013 4:24 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008 Posts: 17447
|
nukalas2010 wrote: |
fjb_saper wrote: |
Your input is not a date. |
Yes, My Input is not a date. But to check whether the Requestdate is greater than or equal to the CURRENT_DATE it should be in Date format. right?? Sorry if I miss something here..  |
This is still not a DATE
Code: |
SET dDate = TRIM(FIELDVALUE(InputRoot.XMLNSC.Request.Date)); |
So that will fail. |
|
Back to top |
|
 |
Vitor |
Posted: Tue Mar 12, 2013 5:49 am Post subject: Re: Check the Date value |
|
|
 Grand High Poobah
Joined: 11 Nov 2005 Posts: 26093 Location: Texas, USA
|
nukalas2010 wrote: |
Is there any better way to achive this ?? |
I would not say "better" but you could consider this:
Code: |
DECLARE dDate DATE;
SET dDate = CAST(TRIM(FIELDVALUE(InputRoot.XMLNSC.Request.Date)) AS DATE DEFAULT DATE DEFAULT DATE '1475-03-06');
IF dDate <> DATE '1475-03-06' THEN
IF (dDate >= CURRENT_DATE) THEN
SET Environment.Variables.Status = 1000;
ELSE
SET Environment.Variables.Status = 1001;
END IF;
ELSE
-- whatever invalid date handling you want
END IF;
|
So unless you do business with Mr & Mrs Michelangelo you should detect odd dates. _________________ Honesty is the best policy.
Insanity is the best defence. |
|
Back to top |
|
 |
nukalas2010 |
Posted: Wed Mar 13, 2013 3:10 am Post subject: Re: Check the Date value |
|
|
 Master
Joined: 04 Oct 2010 Posts: 220 Location: Somewhere in the World....
|
Vitor wrote: |
nukalas2010 wrote: |
Is there any better way to achive this ?? |
I would not say "better" but you could consider this:
Code: |
DECLARE dDate DATE;
SET dDate = CAST(TRIM(FIELDVALUE(InputRoot.XMLNSC.Request.Date)) AS DATE DEFAULT DATE DEFAULT DATE '1475-03-06');
IF dDate <> DATE '1475-03-06' THEN
IF (dDate >= CURRENT_DATE) THEN
SET Environment.Variables.Status = 1000;
ELSE
SET Environment.Variables.Status = 1001;
END IF;
ELSE
-- whatever invalid date handling you want
END IF;
|
So unless you do business with Mr & Mrs Michelangelo you should detect odd dates. |
 |
|
Back to top |
|
 |
|