Birthday Ordering - SQL Server

Question

Good night!

I have an SQL Server Database with fields: Name and Birth Date.

I must sort by the Birth Date, being the order of the nearest date to the most distant date, the intention in this order is to know the next birthday until next year (based on the current date). I’ve tried some Selects, but they all order considering the Year of birth.

1. Select * from Tabelax ORDER By Birthday DESC or ASC
2. Select * from Tabelax ORDER BY DAY(Birthday), MONTH(Birthday)
3. Select * from Tabelax ORDER By Datediff(Month,getdate(), Birthday) ASC or DESC

Thanks in advance for the help.

Answer 1

In the article Dominating dates and times in SQL Server you can find information on how to manipulate dates in SQL Server, including with an item dealing precisely with birthday relationships. From that article I copied the following T-SQL code:

-- código #4.22 v2
-- emite relação de aniversariantes dos próximos dias
--
-- Autor: José Diz 
-- Publicado em: Porto SQL - https://portosql.wordpress.com/blog/

-- informa quantidade de dias
declare @NDias tinyint;
set @NDias= 15;

-- gera datas com limites do período de emissão
declare @Amanha date, @Ate date;
set @Amanha= dateadd (day, +1, cast (sysdatetime() as date));
set @Ate= dateadd (day, (@NDias -1), @Amanha);

-- obtém ano inicial e ano final
declare @Ano_I int, @Ano_F int;
set @Ano_I= year (@Amanha);
set @Ano_F= year (@Ate);

--
IF (@Ano_I = @Ano_F)
  -- período de emissão no mesmo ano
  SELECT Nome, convert (char(5), Data_nasc, 103) as [Dia/Mês]
    from dbo.Cadastro
    where dateadd (year,   
                   (@Ano_I - year (Data_nasc)),
                   Data_nasc) between @Amanha and @Ate
    order by month (Data_nasc), day (Data_nasc)

else
  -- período de emissão cruza ano
  SELECT Nome, convert (char(5), Data_nasc, 103) as [Dia/Mês]
    from dbo.Cadastro
    where dateadd (year,    
                   (@Ano_I - year (Data_nasc)),
                   Data_nasc) >= @Amanha
          or dateadd (year,    
                      (@Ano_F - year (Data_nasc)),
                      Data_nasc) <= @Ate
    order by dateadd (year, (@Ano_I - year (Data_nasc), Data_nasc);

.

What the T-SQL code does is bring the date of birth to the current year (or following year if the period involves more than one year) and then compare with the dates that delimit the period.

In the original SQL code there was no ordering. I added it in this answer but did not test; I hope it is correct.

Answer 2

If it’s just the birthdays that will happen from now on try:

SELECT * FROM TabelaX 
WHERE MONT(Aniversario) * 100 + DAY(Aniversario) > MONTH(getdate()) * 100 + DAY(getdate()) 
ORDER BY MONTH(Aniversario), DAY(Aniversario)