| 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 |
|
| Back to top |
|
 |
| JT |
| 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; |
|
|
| Back to top |
|
|
| 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; |
|
| Back to top |
|
|
| JT |
| 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). |
|
| Back to top |
|
|
| 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 .... |
|
| Back to top |
|
|
|
|
