Day and month comparison on SQL Server

Question

I need to filter a query of every client who has a birthday between two informed dates. How can I make a comparison of dates disregarding the year registered with the client?

The current consultation is like this:

Parameters informed: Start Date:29/04, End Date:05/05

Where Convert(datetime,
        Convert(varchar, DAY(cli.DataNascimento)) + '/' +
        convert(varchar, MONTH(cli.DataNascimento)) + '/1900', 103)
    >= convert(datetime, '29/04/1900', 103)
And Convert(datetime,
        Convert(varchar, DAY(cli.DataNascimento)) + '/' +
        convert(varchar, MONTH(cli.DataNascimento)) + '/1900', 103)
    <= convert(datetime, '05/05/1900', 103)

But this query is returning the following error:

Mensagem 242, Nível 16, Estado 3, Linha 1
A conversão de um tipo de dados varchar em um tipo de dados datetime resultou em um valor fora do intervalo.

Is there any better way to do this consultation?

Answer 1

Considering that the Date birth column is declared as datetime, the suggestion is that the WHERE clause:

WHERE cast(dateadd(year, -year(cli.DataNascimento) % 1900, cli.DataNascimento) as date) between @DataInicial and @DataFinal

This is the context:

-- código #1
--> informe data inicial e final
declare @DataInicial date, @DataFinal date;
set @DataInicial= convert(date, '29/4/2018', 103);
set @DataFinal= convert(date, '5/5/2018', 103);

-- ajuste interno para 1900
set @DataInicial= dateadd(year, -year(@DataInicial) % 1900, @DataInicial);
set @DataFinal= dateadd(year, -year(@DataFinal) % 1900, @DataFinal);

SELECT ...
  from ... as cli
  where cast(dateadd(year, -year(cli.DataNascimento) % 1900, cli.DataNascimento) as date) between @DataInicial and @DataFinal;

Answer 2

I advise to review how the parameter is passed, because conceptually speaking to search between dates you need to receive dates, in case you are deducing somehow the dates.

It is even possible to make this search fix 1900 as the initial date and the current year as the final date (as there is no way that anyone could have been born next year).

The performance of this search will be bad considering that you will bring virtually everything, probably not change much between a search without the date condition on where.

Example:

SET @param1 = @param1 + '/1900'
SET @param2 = @param2 + '/' + CONVERT(VARCHAR, YEAR(GETDATE()))

Your condition where:

WHERE 
   cli.DataNascimento >= CONVERT(datetime, @param1 , 103)
   AND  cli.DataNascimento <= CONVERT(datetime, @param2 , 103)

Example applied in a trial:

Answer 3

I would simplify the logic somewhat by adding the amount of years left to the current year at the customer’s date of birth. Once you were receiving as parameter the start and end dates considering the current year, it would be a simple comparison of dates.

Look at that:

SELECT 1
WHERE DATEADD(YEAR,DATEDIFF(YEAR, cli.DataNascimento,GETDATE()), cli.DataNascimento) 
    BETWEEN 
        CAST('20180429' as datetime) // Data inicial do período buscado
        AND 
        CAST('20180505' as datetime) // Data final do período buscado

As a small table test, we can use a customer born in 14/10/1990. So by targeting logic we would have:

// Datas como string no formato YYYYMMDD

DATEDIFF(YEAR, '19901014',GETDATE()) 
// Resultado: 2018 - 1990 = 28

DATEADD(YEAR, 28 , '19901014')
// Resultado: 14-10-2018

// Agora bastou comparar:
'20181014' BETWEEN CAST('20180429' as datetime) AND CAST('20180505' as datetime)
// O resultado é: False

I hope I’ve helped.

Answer 4

We found that the problem was the day 29 February registered on the date of birth of some customers. As the year 1900 is not a leap year gave error when converting 29/02/1900 to date. So the filter is as follows:

Where 1=1
    And Convert(datetime, Format(cli.DataNascimento, 'dd/MM') + '/1904', 103) 
        >= convert(datetime, '10/05/1904', 103)  
    And Convert(datetime, Format(cli.DataNascimento, 'dd/MM') + '/1904', 103) 
        <= convert(datetime, '12/05/1904', 103)