Avatar of Aleks
Flag for United States of America

asked on 

Problem with comparing dates

I have a stored procedure that compares a date in the database to two dates entered by the user. If the date in the database is between those two dates the result should show.

I need is to fix the query below so that

1. priority_start is left empty and priority_end is left empty then return all results. The problem here is fields that are null, those should be returned as well.

2. User enters both fields start and end, then the results should show the matching records MINUS the null records


AND ( a.PrioDate BETWEEN @priority_start AND @priority_end ) or a.PrioDate is null

Open in new window

This is the part that I am missing in my query, right now I get the results + null even when user enters the dates.

How can I fix this ?

I tried:

AND ( a.RcptDate BETWEEN @recpt_start AND @recpt_end AND RcptDate IS NOT null ) or a.RcptDate is null

but that did not work.

Notice that RcptDate   is a datetime field. I am passing dates such as '01/01/2010'  not sure if this is an issue.
Web DevelopmentMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment

8/22/2022 - Mon