MQSeries.net :: View topic - Compare dates and date addition/subtraction

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Compare dates and date addition/subtraction

Compare dates and date addition/subtraction

« View previous topic :: View next topic »

Author

Message

klabran

Posted: Tue Jan 18, 2005 1:26 pm Post subject: Compare dates and date addition/subtraction

Master

Joined: 19 Feb 2004
Posts: 259
Location: Flagstaff AZ

Is it possible to do date math in mqsi 2.1?

I want to subtract 18 years from an incoming date and then compare that result to another date.

How would I do this in mqsi 2.1?

Kevin

Posted: Tue Jan 18, 2005 3:02 pm Post subject:

Padawan

Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.

Quote:

I want to subtract 18 years from an incoming date....

Take a look at the INTERVAL data type.

An example would be:

Code:

SET OutputRoot.XML.adjustedDate = CURRENT_DATE - INTERVAL '18' MONTH;

klabran

Posted: Tue Jan 18, 2005 3:19 pm Post subject:

Master

Joined: 19 Feb 2004
Posts: 259
Location: Flagstaff AZ

I have the following in my filter but it is erroring with...
Type mismatch assigning value %1 to variable whilst executing %2.
Variable is of type %3.

It is complaining about dt but I am not sure how to fix....

Is it the comparison? dt < dt1 ????

do dates have to be yyyy-mm-dd? mine are coming in as mm-dd-yyyy

Any ideas?

DECLARE dt DATE;
DECLARE dt1 DATE;

SET dt = Body.Arrest.ActivityDate;
SET dt1 = Body.Arrest.ActivitySuspect.PersonBirthDate;

IF dt - INTERVAL '18' YEAR < dt1 THEN
-- under 18
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;

Posted: Tue Jan 18, 2005 7:08 pm Post subject:

Padawan

Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.

Try this instead:

Code:

SET dt = CAST(Body.Arrest.ActivityDate AS DATE);
SET dt1 = CAST(Body.Arrest.ActivitySuspect.PersonBirthDate AS DATE);

Also, I'm not sure this piece of code is going to work correctly:

Code:

IF dt - INTERVAL '18' YEAR < dt1 THEN

You may have to set the first operand to a DATE variable, prior to comparing it to another DATE variable (dt1).

klabran

Posted: Wed Jan 19, 2005 7:21 am Post subject:

Master

Joined: 19 Feb 2004
Posts: 259
Location: Flagstaff AZ

Since my dates are coming in mm/dd/yyyy I had to convert them to yyyy-mm-dd.

Cast didn't work... I assume because of the incoming format.

I then moved the interval out of the compare and now it works....

Thanks everyone!

SET dt = substring(Body.Arrest.ActivityDate from 7 for 4) || '-' || substring(Body.Arrest.ActivityDate from 1 for 2) || '-' || substring(Body.Arrest.ActivityDate from 4 for 2)
SET dt1 = substring(Body.Arrest.ActivitySuspect.PersonBirthDate from 7 for 4) || '-' || substring(Body.Arrest.ActivitySuspect.PersonBirthDate from 1 for 2) || '-' || substring(Body.Arrest.ActivitySuspect.PersonBirthDate from 4 for 2)

SET dt = dt - INTERVAL '18' YEAR;

IF dt < dt1 THEN ....

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Compare dates and date addition/subtraction

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Protected by Anti-Spam ACP