DateDiff()
A user wanted an MS SQL-style DateDiff in Informix and got "Non-numeric character in datetime or interval" (and later -1260 conversion errors) when subtracting a quoted date string and comparing the result to the plain number 60. The fix offered by Mark Stock worked: keep the comparison in datetime terms, e.g. WHERE (EXTEND(referral_date, YEAR TO DAY) - 60 UNITS DAY) = DATETIME(2003-02-06) YEAR TO DAY. Another poster noted that with DATE columns simply "current - referral_date = 60" works, but suggested comparing referral_date to a precomputed date instead so an index can be used.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
How do I do a DateDiff function like in MsSQL? I
have tried the following:
select * from accounts where (extend(referral_date, year to day) -extend('02/06/2003', year to day)) = 60
but it is returning an error "Non-numeric character in dtetime or interval"
Robert Phillips
Systems Analyst
Chamberlin Edmonds and Associates (www.ce-a.com)
404-634-5196 x1261
Nope.... Error -1260: It is not possible to convert
between the specified
types. It highlights 60 ?
-----Original Message-----
From: Rajamani Muralidharan [mailto:rmurali@us.ibm.com]
Sent: Thursday, February 06, 2003 10:41 AM
To: Phillips, Rob
Subject: Re: DateDiff() [266]
Try
select * from accounts
where (extend(referral_date, year to day) - datetime (2003-02-06) year today ) = 60
Regards,
Raj
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Raj Muralidharan Voice: 301 803 2905
Consulting IT Specialist Email: rmurali@us.ibm.com
IBM Data Management T/L: 262-2905
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Phillips, Rob"
<RPhillips@ce-a.c To: ids@iiug.org
om> cc:
Sent by: Subject: DateDiff() [266]
forum.subscriber@
iiug.org
02/06/2003 10:11
AM
How do I do a DateDiff function like in MsSQL? I have tried the following:
select * from accounts where (extend(referral_date, year to day) -extend('02/06/2003', year to day)) = 60
but it is returning an error "Non-numeric character in dtetime or interval"
Robert Phillips
Systems Analyst
Chamberlin Edmonds and Associates (www.ce-a.com)
404-634-5196 x1261
Phillips, Rob wrote:
> How do I do a DateDiff function like in MsSQL? I have tried the following:
>
> select * from accounts where (extend(referral_date, year to day) -> extend('02/06/2003', year to day)) = 60
>
> but it is returning an error "Non-numeric character in dtetime or interval"
If these are DATETIME values, then try something like:
SELECT *
FROM accounts
WHERE (EXTEND(referral_date, YEAR TO DAY) - 60 UNITS day)
= DATETIME(2003-02-06) YEAR TO DAY
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd http://MydasSolutions.com |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+
Nope that didn't work either. It returned nothing
whe I know that there are
many records with a referral date 60 days before today.
-----Original Message-----
From: Mark D. Stock [mailto:mdstock@mydassolutions.com]
Sent: Thursday, February 06, 2003 11:53 AM
To: ids@iiug.org
Subject: Re: DateDiff() [269]
Phillips, Rob wrote:
> How do I do a DateDiff function like in MsSQL? I have tried the
following:
>
> select * from accounts where (extend(referral_date, year to day) -> extend('02/06/2003', year to day)) = 60
>
> but it is returning an error "Non-numeric character in dtetime or
interval"
If these are DATETIME values, then try something like:
SELECT *
FROM accounts
WHERE (EXTEND(referral_date, YEAR TO DAY) - 60 UNITS day)
= DATETIME(2003-02-06) YEAR TO DAY
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd http://MydasSolutions.com |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+
Ops nevermind... this did work. Thanks Mark.
-----Original Message-----
From: Mark D. Stock [mailto:mdstock@mydassolutions.com]
Sent: Thursday, February 06, 2003 11:53 AM
To: ids@iiug.org
Subject: Re: DateDiff() [269]
Phillips, Rob wrote:
> How do I do a DateDiff function like in MsSQL? I have tried the
following:
>
> select * from accounts where (extend(referral_date, year to day) -> extend('02/06/2003', year to day)) = 60
>
> but it is returning an error "Non-numeric character in dtetime or
interval"
If these are DATETIME values, then try something like:
SELECT *
FROM accounts
WHERE (EXTEND(referral_date, YEAR TO DAY) - 60 UNITS day)
= DATETIME(2003-02-06) YEAR TO DAY
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd http://MydasSolutions.com |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+
If the intent is to select all accounts that are 60 days old, I'd think
that the following syntax would work:
select *
from accounts
wherecurrent - referral_date = 60;
The only challenge here is that can't be an indexed read, so if the
accounts table is large, this isn't terribly efficient. I haven't done
this in a while, so I don't know if you can build an index on that
expression (functional index) or what that syntax might be.
The other way to approach this would be to figure out current - 60, and
then compare referral date directly to that. That would allow you to use
the index.
Hope that helps.
Dan Michaelis
813.978.6534 (office) <== Please note the change
813.303.3225 (pager)
dan.michaelis@verizon.com
"Phillips, Rob"
<RPhillips@ce-a.c To: ids@iiug.org
om> cc:
Sent by: Subject: RE: DateDiff() [267]
forum.subscriber@
iiug.org
02/06/2003 10:51
AM
Nope.... Error -1260: It is not possible to convert between the specified
types. It highlights 60 ?
-----Original Message-----
From: Rajamani Muralidharan [mailto:rmurali@us.ibm.com]
Sent: Thursday, February 06, 2003 10:41 AM
To: Phillips, Rob
Subject: Re: DateDiff() [266]
Try
select * from accounts
where (extend(referral_date, year to day) - datetime (2003-02-06) year today ) = 60
Regards,
Raj
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Raj Muralidharan Voice: 301 803 2905
Consulting IT Specialist Email: rmurali@us.ibm.com
IBM Data Management T/L: 262-2905
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Phillips, Rob"
<RPhillips@ce-a.c To: ids@iiug.org
om> cc:
Sent by: Subject: DateDiff() [266]
forum.subscriber@
iiug.org
02/06/2003 10:11
AM
How do I do a DateDiff function like in MsSQL? I have tried the following:
select * from accounts where (extend(referral_date, year to day) -extend('02/06/2003', year to day)) = 60
but it is returning an error "Non-numeric character in dtetime or interval"
Robert Phillips
Systems Analyst
Chamberlin Edmonds and Associates (www.ce-a.com)
404-634-5196 x1261