Ask Your Question
0

Query, add 30 days to date, then compare [closed]

asked 2018-03-14 20:43:32 +0100

Joe Castor gravatar image

The goal is to test two date, reporting when the first date + 30 days is greater than the second date. I have tried several versions of the following but can't make it work. The error indicates a problem with "NewDate". Any help greatly appreciated.

SELECT "MemberID" "MemberID", "LastName" "LastName", "FirstName" "FirstName", "DateJoined" "DateJoined", "LastRenewalDate", "MemberThruDate" "MemberThruDate", DATEDIFF('dd','12-30-1899',"LastRenewalDate") +30 AS "NewDate" FROM "MemberMaster" "MemberMaster" WHERE "FirstName" != '|' HAVING "NewDate" > "MemberThruDate" ORDER BY "MemberID" ASC
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Joe Castor
close date 2018-03-15 02:32:37.639310

1 Answer

Sort by » oldest newest most voted
0

answered 2018-03-14 21:15:14 +0100

Ratslinger gravatar image

updated 2018-03-14 21:51:26 +0100

Hello,

My memory seems to recall you using a split DB. With that, this will work:

SELECT "MemberID" "MemberID",
       "LastName" "LastName",
       "FirstName" "FirstName",
       "DateJoined" "DateJoined",
       "LastRenewalDate",
       "MemberThruDate" "MemberThruDate"
FROM "MemberMaster"
WHERE "FirstName" != '|' and (DATEADD('day',30,"LastRenewalDate") > "MemberThruDate")
ORDER BY "MemberID" ASC

If not and you are using an embedded DB then:

SELECT "MemberID" "MemberID",
       "LastName" "LastName",
       "FirstName" "FirstName",
       "DateJoined" "DateJoined",
       "LastRenewalDate",
       "MemberThruDate" "MemberThruDate",
FROM "MemberMaster"
WHERE "FirstName" != '|' and (DATEDIFF('dd',"MemberThruDate","LastRenewalDate") > 30)
ORDER BY "MemberID" ASC
edit flag offensive delete link more

Comments

Thanks so much. Yes, a split DB. I read in the Query manual that there was no DATEADD function. It obviously refers to the embedded DB. Anyway, this worked perfectly. PS - The DB is finally up and running and things are working well, thank you very much for all your help and support.

Joe Castor gravatar imageJoe Castor ( 2018-03-15 00:33:52 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2018-03-14 20:43:32 +0100

Seen: 34 times

Last updated: Mar 14 '18