using BETWEEN in sql2005
-
I have an sql statement comparing dates in the Where clause. This is my code: [CODE] CONVERT(NVARCHAR, D.Spd_deldateans, 101) between @DATE1 AND @DATE2[/CODE] The problem with the statement, is that, it doesn't show any records when one of the parameters is blank or null. How would it be able to show records giving value only to either of the parameters?
-
I have an sql statement comparing dates in the Where clause. This is my code: [CODE] CONVERT(NVARCHAR, D.Spd_deldateans, 101) between @DATE1 AND @DATE2[/CODE] The problem with the statement, is that, it doesn't show any records when one of the parameters is blank or null. How would it be able to show records giving value only to either of the parameters?
toink toink wrote:
The problem with the statement, is that, it doesn't show any records when one of the parameters is blank or null. How would it be able to show records giving value only to either of the parameters?
That is correct. A NULL in a set based operation always yields NULL.
toink toink wrote:
CONVERT(NVARCHAR, D.Spd_deldateans, 101) between @DATE1 AND @DATE2
Why are you converting Db.Spd_deldateans to an NVARCHAR? If D.Spd_deldateanes is already a DATETIME or SMALLDATETIME then
WHERE
(D.Spd_deldateans >= @DATE1 OR @DATE1 IS NULL) AND
(D.Spd_deldateans <= @DATE2 OR @DATE2 IS NULL)What this does is say the equivalent of IF D.Spd_deldateans is greater than or equal to @DATE1 OR @DATE1 IS NULL which evaluates to TRUE if @DATE1 is null which means that the >= operation is effectively overriden
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog