It's giving me an error when I make this query to compare the anniversary day and month in sql format(data,'MM-dd') .
I need to draw the date the month and the year and make a between
Select NUMERO,DATA from utilizadores where format(DATA ,'MM-dd') between '12-30' and '01-02'
I want to make a birthday list, the format does not work in some versions of sql
Another error is when the year changes
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 set the issuing period, as this makes it easier to handle month and year turn. The user's date of birth is updated to the year of the issuing period and then verified if it is in the period.
Assuming that the issue period is 1/2/2017 to 10/2/2017, and if the user of number 8 was born on 9/19/1960, the date is transformed into "19/9 / 2017 "and then compared to the period limitation dates:
se "19/9/2017" está entre "1/2/2017" e "10/2/2017"
então "é aniversariante";
Try filtering year and month as shown 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;