2 Date Parameters that could be NULL...

iWilma 21 Reputation points
2021-09-01T15:54:47.37+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
0 comments No comments
{count} votes

Accepted answer
  1. Paul Staniforth 76 Reputation points
    2021-09-01T16:17:28.123+00:00

    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

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. iWilma 21 Reputation points
    2021-09-01T16:25:46.127+00:00

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

    0 comments No comments