Day and month comparison in SQL Server

We verified that the problem was February 29 registered on the date of birth of some clients. Since the year 1900 is not a leap year it was an error when converting 29/02/1900 to date. So the filter was 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)

Considering that the DateNavigation column is declared as datetime , the suggestion is that the WHERE clause looks like this:

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

Here's the contextualization:

-- 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;

I would rather simplify the logic by adding the amount of years that are missing for the current year on the customer's date of birth. Once you were getting the start and end dates for the current year as a parameter, it would be a comparison of simple dates.

See:

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 client born in 14/10/1990 . So by segmenting the 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 have helped.

I advise you to review how the parameter is passed, since conceptually speaking to search between dates you need to receive dates, in case you are deducting the dates in some way.

You can even do this search if you set 1900 as the start date and the current year as the end date (because you can not have anyone born next year).

The performance of this search will be poor considering that you will bring almost everything, probably will not change much between a search without the date condition in 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 to a procedure: