Validate data in sql server

Question

I get a date, and I need to know if it’s valid, if it’s not, I need to shorten her day until it’s valid.

set @data = (select convert(date,CONCAT(@dia,'-',month(@vencimento1),'-',year(@vencimento1))))
set @vencimento = (select DATEADD(month, 1, @data))

I tried something like:

if (select isdate(DATEADD(month, 1, @data))) = 1 
    set @vencimento = (select DATEADD(month, 1, @data))
else
    set @vencimento = (select dateadd(day, -1, @data), DATEADD(month, 1, @data))

But it doesn’t work either. What would be the best way to check ?

EDIT

I arrived at this code with the junction of the answers:

while(isdate((CONCAT(@dia,'-',month(@vencimento1),'-',year(@vencimento1)))) = 0 ) 
        set @dia = @dia - 1;
        set @data = (select convert(date,CONCAT(@dia,'-',month(@vencimento1),'-',year(@vencimento1))))
        print @data
        set @vencimento = (select DATEADD(month, 1, @data))

But what happens, it does not generate the correct day, it was to generate for example on 31/12, it generates pro day 30/12, and on January 31/01, and in February it takes day 29.

Answer 1

As it stands, if the date is not valid (considering your if validation), you are setting two values for the variable @vencimento ((select dateadd(day, -1, @data), DATEADD(month, 1, @data))). This is the error shown!

Leaves only the first expression that should solve what you need:

if (select isdate(DATEADD(month, 1, @data))) = 1 
    select @vencimento = DATEADD(month, 1, @data)
else
    select @vencimento = DATEADD(day, -1, @data)

Take a look in this test.

---

edited

Considering @data being the type date (see comment on this reply), a cast to be used by the function dateadd (another example):

if (select isdate(DATEADD(month, 1, CAST(@data AS datetime)))) = 1 
    select @vencimento = DATEADD(month, 1, CAST(@data AS datetime))
else
    select @vencimento = DATEADD(day, -1, CAST(@data AS datetime))

Answer 2

It could be done like this:

declare @dia varchar(2)
declare @mes varchar(2)
declare @ano  varchar(4)
declare @data date


set @dia = 40;
set @mes = 80;
set @ano = 2019

begin
   if ( @dia > 31 ) set @dia = 31
   if ( @mes > 12 ) set @mes = 12
   if ( @ano > 2019 ) set @ano = 2019


   while ( isdate( concat( @mes, '-', @dia, '-', @ano )) = 0 ) 
      set @dia = @dia - 1;

   set @data = cast( concat( @mes, '-', @dia, '-', @ano ) as date )
   print concat( @mes, '-', @dia, '-', @ano )

   print @data
end