2 Date Parameters that could be NULL...

Question

I have 2 date parameters that could have NULL values. I have an integer that could be NULL.
In my WHERE CLAUSE, how do I check IS NOT NULL using BETWEEN dates.
Also how do I check to see if the integer IS NOT NULL?
I am using SQL 2014 version.

Note: If I hard code the begin and end dates the statements works, but if I use the parameters the statement does not work. Each of the parameters could be null.

Example Code

@BeginDate DateTime = NULL,
@EndDate DateTime = NULL,
@xBackLogYear int = NULL

SELECT Id, CreateDate, Name , BackLogYear FROM [dbo].[MyTable]
WHERE CreateDate BETWEEN CONVERT(VARCHAR, @BeginDate, 101)
AND CONVERT(VARCHAR, @EndDate, 101)
OR (@xBackLogYear IS NOT NULL OR BackLogYear = @xBackLogYear )
AND [Name] = 'Billison'

Please provide your expertise with fixing my codes. Thank you

Answer 1

Hi,

A simple option would be to wrap your Convert statements for the date with an isnull(<value>,<default>) and provide a default value for nulls.

e.g. if you wanted all dates returned if the date fields are null you could use e.g. for the from date : ISNULL(Convert(varchar,@Begindate,112), '19000101')

this would default the from date to be the 1st Jan 1900.

For the TO Date the default could be today : ISNULL(Convert(varchar,@EndDate,112), convert(varchar, getdate(), 112)

PS: I have used 112 in the convert statements in my examples above as this produces an ISO date in the form YYYYMMDD which will be independent of regional settings

Answer 2

Great answer this issue is resolved - Thank you very much.