How to query birthday of the day and month in SQL in Oracle?

Complementing @Ricardo's answer , one of the ways to do this is to use EXTRACT. Since you want to "consult birthday boy of the day and month", you should extract these fields (Day and month) from the date of birth and compare them with 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, using TO_CHAR, which converts the date to VARCHAR, using some specific format.

According to 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 (August 11), 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 chosen solution, there is still a special case to consider: if a person was born on February 29, 2016 (or February 29 of any other Leap Year), for example. How will your system detect that it is 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(as in other years there is no February 29).

So in non-leap years, you should make some decision:

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

Follows an example query that detects the user's birthday on February 28, but only in non-leap years (if the year is leap, the birthday is normally detected on the day 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')

Remembering that the Leap Year Rule is:

• if the year is multiple of 100, it is only leap if it is multiple of 400
• if it is not multiple of 100, it is only leap if it is multiple of 4