Pull data using date difference between 2 date fields

Krishnadeva Reddy · New User Joined: 14 Nov 2007 Posts: 40 Location: Chennai

Hello,

I'm trying to pull data based on the difference between 2 date fields. If Exp_dte is greater than 1 year of Eff_dte then the query should produce the results. Could someone please assist me on this? I tried using DATEDIFF and also in where clause just put the difference between two dates but I either get "SQLCODE = -440, ERROR: NO AUTHORIZED FUNCTION NAMED DATEDIFF HAVING COMPATIBLE ARGUMENTS WAS FOUND" or "SQLCODE = -182, ERROR: AN ARITHMETIC EXPRESSION WITH A DATETIME VALUE IS INVALID"

Sample Table:

sergeyken · Posted: Sat Aug 17, 2024 1:44 am

Please, present the real code of your attempts.

Krishnadeva Reddy · New User Joined: 14 Nov 2007 Posts: 40 Location: Chennai

Queries I tried:

SELECT Clm_Num, Eff_dte, Exp_dte from table1 where DATEDIFF(Exp_dte,Eff_dte) > 1 YEAR;

SELECT Clm_Num, Eff_dte, Exp_dte from table1 where
Exp_dte - Eff_dte > 1 YEAR;

Pedro · Posted: Sat Aug 17, 2024 4:56 am

re: "NO AUTHORIZED FUNCTION NAMED DATEDIFF HAVING COMPATIBLE ARGUMENTS WAS FOUND"

How are the two columns defined? And what are the acceptable arguements for DATEDIFF?

Krishnadeva Reddy · New User Joined: 14 Nov 2007 Posts: 40 Location: Chennai

Both columns are defined as DATE type.

Is there any other way to get the output through SQL?

sergeyken · Posted: Mon Aug 19, 2024 12:34 am

I posted the required syntax for DATEDIFF - as quoted from the User’s Guide, but my post has been deleted by moderators.

Pedro · Posted: Mon Aug 19, 2024 7:59 am

I forgot what Sergey had posted... but I found an example, something like this: