sql server - How to check a date range in SQL? -

i query database check if date within range of 2 dates. there start date , end date. essentially, display values using between keyword.

i date formats match well. @date1 20150520

declare @startdate date declare @enddate date declare @date1 date declare @finaldate varchar(1)  set @startdate = dateadd(day, -5, getdate()) set @enddate = getdate() set @date1 = '20150520'    select record table1 @date1 < @enddate , @date1 > @startdate 

this seems make sense. though when try use if conditional null using code below:

if @date1 > @startdate , @date1 < @enddate begin set @finaldate = '1' end else if @date1 > @enddate begin set @finaldate = '2' end  select @finaldate final_date 

though reason, @finaldate equates null...which not expect conditional branch.

you need additional branch on conditional, if you're cover bases:

else if @date1 <= @startdate 

you've tested 1) between 2 dates , 2) greater last date. @ least 1 of should include equality check, well, or else if date equal won't match up.

now, could go plain old else block @ end, catch everything, suspect you're looking more like:

if @date1 > @startdate , @date1 < @enddate     begin         set @finaldate = '1'     end else if @date1 >= @enddate     begin         set @finaldate = '2'     end else if @date1 <= @startdate     begin         set @finaldate = '3'     end  select @finaldate final_date