Member Avatar for solomon_13000

I am attempting to retrieve records 7 days before the termination date. The code below doesn't seem to work.

CREATE VIEW vwRenewSubscription
AS
  SELECT c1.id as userId, p1.id subscriptionId,
  a1.id as advertisementPlanId,
  c1.companyName, c1.rocNo,
  a1.description, a1.fees,a1.duration
  FROM subscription AS p1, subscription AS p2, corporate AS c1, advertisementplan AS a1
  WHERE p1.id = p2.subscription_id_fk AND p1.id = p2.subscription_id_fk
  AND p1.user_id_fk = c1.id
  AND a1.id = p1.advertisementPlan_id_fk
  AND date_format(now(), '%Y-%m-%d') < p1.terminationDate
  AND date_format(now(), '%Y-%m-%d') > date_format(date_sub(p1.terminationDate, interval 7   day), '%Y-%m-%d');

Your help is kindly appreciated.

Thank You.

  • 2 Contributors
  • 1 Reply
  • 106 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for smantscheff

Do not convert NOW() and terminationDate into strings for date arithmetics and comparisons.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.