Birthday listing in a date range

Question

You are giving me an error when I do this query to compare the day and month of anniversary in sql format(data,'MM-dd') . need to take from date the month and year and make a between

Select NUMERO,DATA from utilizadores where  format(DATA ,'MM-dd') between '12-30' and '01-02'

I intend to make a birthday listing the format does not work in some versions of sql

Another mistake is when the year changes

Answer 1

In this case:

-- código #1 v5
declare @DataInicial date, @DataFinal date;
set @DataInicial = cast(Current_timestamp as date);
set @DataFinal = @DataInicial;

-- verifica se é sexta-feira
set datefirst 7; -- domingo como primeiro dia da semana
IF datepart(weekday, @DataInicial) = 6
  set @DataFinal = DateAdd(day, +2, @DataInicial);    

--
declare @Ano1 int, @Ano2 int;
set @Ano1= year(@DataInicial);
set @Ano2= year(@DataFinal);

--
IF (@Ano1 = @Ano2)
  begin
  SELECT NUMERO, Convert(char(5), DATA, 103) as [Dia/Mês]
    from utilizadores 
    where DATA < @DataInicial
          and Cast(DateAdd(year, (-year(DATA) + @Ano1), DATA) as date) between @DataInicial and @DataFinal 
    order by month(DATA), day(DATA)
  end
else
IF (@Ano1 < @Ano2)
  begin
  SELECT NUMERO, Convert(char(5), DATA, 103) as [Dia/Mês]
    from utilizadores 
    where DATA < @DataInicial 
          and (Cast(DateAdd(year, (-year(DATA) + @Ano1), DATA) as date) >= @DataInicial 
               or Cast(DateAdd(year, (-year(DATA) + @Ano2), DATA) as date) <= @DataFinal)
    order by month(DATA), day(DATA)
  end; 

I chose to use variables of type date to define the period of issue, as this makes it easier to deal with month and year changes. The user’s date of birth is updated to the year of the issuing period and then it is checked if it is in the period.

Assuming the period of issue is 1/2/2017 to 10/2/2017, and if the number 8 user was born in "9/19/1960", the date is transformed into "9/19/2017" and then compared with the period limitation dates:

se "19/9/2017" está entre "1/2/2017" e "10/2/2017"
     então "é aniversariante";

Answer 2

Try to filter year and month as example below!

SET DATEFORMAT DMY;

DECLARE
    --@ANO  INT;
    @MES    INT;

--SET   @ANO    = 2017;
SET @MES    =   2; -- fevereiro


SELECT
    U.NUMERO,
    U.DATA

FROM
    UTILIZADORES U

WHERE
    -- DATEPART(YEAR, U.DATA) = @ANO
    DATEPART(MONTH, U.DATA) = @MES;