How to consult birthday and month in SQL on Oracle?

Question

I saw some examples here, and tried to reproduce them, however, on Oracle does not accept operators as MONTH, then I was unsuccessful.

SELECT nome from aluno where month(datanascimento) = month(now());

Answer 1

Complementing the reply from @Ricardo, one of the ways to do it is to use EXTRACT. As you want to "consult day and month birthday", you should extract these fields (day and month) from the date of birth and compare them to the current date (sysdate):

SELECT nome FROM aluno
WHERE extract(month from datanascimento) = extract(month from sysdate)
      AND extract(day from datanascimento) = extract(day from sysdate)

---

Another way is to get the day and month at once by using TO_CHAR, which converts the date to VARCHAR, using some specific format.

According to the documentation, we can use the format MM-DD (MM corresponds to the month and DD to the day). Thus, the query would look like this:

SELECT nome FROM aluno
WHERE to_char(datanascimento, 'MM-DD') = to_char(sysdate, 'MM-DD')

For example, for today’s date (11 August), to_char(sysdate, 'MM-DD') returns 08-11 (no matter what the year is). Therefore, if the date of birth is also August 11 (even if it is in another year), the result of to_char will also be 08-11.

---

Leap years

Regardless of the solution chosen, there is still a special case to consider: if a person was born in 29 de February de 2016 (or 29 February of any other leap year), for example. How your system will detect that it’s this person’s birthday in non-leap years?

If you use the above queries, your system will only detect this user’s birthday in leap years (for in other years there is no February 29).

So in years that are not leap, you should make some decision:

• consider that the birthday is February 28 or March 1 (I know people who were born on February 29th, and in non-leap years they celebrate their birthday on one of those days)
• do nothing (knowing that these users will not have the birthday detected every year)

Here is an example of a query that detects the user’s birthday on February 28, but only in non-leap years (if the year is leap, the birthday is usually detected on February 29):

SELECT a.nome
FROM 
 (SELECT aluno.*,
  case -- verifica se o ano atual é bissexto
    when mod(extract(year from sysdate), 400) = 0
         OR
         (mod(extract(year from sysdate), 100) <> 0
          AND mod(extract(year from sysdate), 4) = 0)
    then 1
    else 0
  end as ano_bissexto
  FROM aluno) a
WHERE
  to_char(a.datanascimento, 'MM-DD') = to_char(sysdate, 'MM-DD')
  OR
  -- nasceu em 29 de fevereiro, ano atual não é bissexto e hoje é 28 de fevereiro
  (to_char(a.datanascimento, 'MM-DD') = '02-29' AND a.ano_bissexto = 0
   AND to_char(sysdate, 'MM-DD') = '02-28')

Recalling that the leap year rule is:

• if the year is a multiple of 100, it shall be leap only if it is a multiple of 400
• if it is not a multiple of 100, it is bisext only if it is a multiple of 4

Answer 2

In the Oracle you can use the EXTRACT:

 SELECT nome 
  FROM aluno
  WHERE extract(month from datanascimento) = extract(month from sysdate)

Here a working example: sqlfiddle.com