CODEWITHSUNDEEP
mysql
Jake Cube
Jake Cube

Reputation: 143

How to get date more than 14 days base on date in column?

Example: Claim Date = 14/3/2017 And today was 29/3/2017 it was more than 14 days. I've tried DATEDIFF in Mysql it keep showing error somehow. 

Example Table:
Table Name: 
**ec_claims**
ec_claims_id Primaryint(11)
ec_claims_date  date

CODE:

SELECT DATEDIFF(NOW(),ec_claims_date) AS DAYGAP FROM ec_claims
WHERE DAYGAP >= 14

Error:

#1054 - Unknown column 'DAYGAP' in 'where clause'

Thank You For Reading This.

Upvotes: 1

Views: 74

Answers (2)

Venkata Krishna
Venkata Krishna

Reputation: 1776

WHERE clause does not allow the use of aliases.

You can either use your entire expression without the alias in the where clause, like:

SELECT DATEDIFF(NOW(),ec_claims_date) AS DAYGAP FROM ec_claims
WHERE DATEDIFF(NOW(),ec_claims_date) >= 14

or alternatively, you can use HAVING in place of WHERE and go ahead with the alias.

SELECT DATEDIFF(NOW(),ec_claims_date) AS DAYGAP FROM ec_claims
HAVING DAYGAP >= 14

Upvotes: 0

Yu Jiaao
Yu Jiaao

Reputation: 4714

Use DATE_ADD function like

SELECT DATE_ADD(now(),INTERVAL -14 DAY) AS fourteenDayAgo

In your case

SELECT ec_claims_date FROM ec_claims
WHERE DATE_ADD(now(),INTERVAL -14 DAY)> ec_claims_date

or like you write, you must repeat it in WHERE clause

SELECT DATEDIFF(NOW(),ec_claims_date) AS DAYGAP FROM ec_claims
  WHERE DATEDIFF(NOW(),ec_claims_date) >= 14

Upvotes: 3

Related Questions