How to check day and month birthday in SQL in Oracle?

In Oracle you can use EXTRACT :

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

Here's a working example: sqlfiddle.com

Complementing reply from @Ricardo , one of the ways to do that is to use EXTRACT . As you want to "check birthday and day of the month", you must 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 in one go, 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 month and DD to day). So, 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). So if the date of birth is also 11 August (even 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 on February 29, 2016 (or February 29 of any other leap year), for example. How will your system detect that this person's birthday is in non-leap years?

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

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

• 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 their birthday on one of those days )
>
• do nothing (knowing that these users will not have their birthday detected every year)

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

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 a multiple of 100, it is only a leap if it is a multiple of 400
• If it is not a multiple of 100, it is only a leap if it is a multiple of 4