CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Hyder
Hyder

Reputation: 11

How to write a SQL query to retrieve all those customers whose age in months is more than 200 months?

How to write a SQL/Oracle query to retrieve all those customers whose age in months is more than 200 months? I have a exam on Monday but I am having some confusion with months and dates calculation.

Upvotes: 1

Views: 114

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

In Oracle DB, there are two nice functions : months_between and add_months been used for these type date calculations. For your case, you may use one of the following :

select id, name, surname
  from customers
 where months_between(trunc(sysdate),DOB)>200;

or

 select id, name, surname
  from customers
 where add_months(trunc(sysdate),-200)>DOB;

demo

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

The logic is the same (the date is older than today minus 200 months), but the actual SQL is usually different, because DBMSes have a large variation of syntax in the date/time area.

Standard SQL & MySQL:

WHERE datecol < current_date - interval '200' month

Oracle:

WHERE datecol < add_months(current_date, -200)

In fact Oracle also supports the Standard SQL version, but it's not recommended, because you might get an invalid date error when you do something like '2018-03-31' - interval '1' month. This is based on a (dumb) Standard SQL rule which MySQL doesn't follow: one month before March 31 was February 31, oops, that date doesn't exists.

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can use a Query like this for MySQL:

SELECT *
FROM yourTable
WHERE bithdayField <= NOW() - INTERVAL 200 MONTH;

Upvotes: 1

Related Questions