Problem with isnull. Need to substitute null if a var is null and compare it to null and return

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @.inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off? Thank you

set ansi_nulls off

go

declare

@.inFileName VARCHAR (100),

@.inFileSize INT,

@.Id int,

@.inlanguageid INT,

@.inFileVersion VARCHAR (100),

@.ExeState int

set @.inFileName = 'A0006337.EXE'

set @.inFileSize = 28796

set @.Id= 1

set @.inlanguageid =null

set @.inFileVersion =NULL

set @.ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @.inFileName

AND DR.FileSize =@.inFileSize

AND DR.FileVersion = isnull(@.inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@.inlanguageid,null)

AND DR.[ID]= @.ID

)

go

set ansi_nulls on

well actually you dont need to change the setting

if you're up to something like this

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,-1)

|||

There is a slight problem with this. If the right side is null, it will evaluate to -1. If the left side is not null, it will evaluate to value stored in the table. It's VERY likely that the value in the table won't be -1. So the condition will be false. But, in actuality, it should be true, correct? Shouldn't it be like this?

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,isnull(DR.FileVersion,-1))

Thank you

|||

with this

AND isnull (DR.FileVersion,-1) = isnull(@.inFileVersion,-1)

the ending equation would be

and (-1 = -1) which evaluates to true.

meaning null=null

remember that this equation resides in the "where clause" and not on the

select clause. if you want to have it returned you must

place a "case clause" in the select statement to evaluate this

nevertheless this clause must still exist in the where clause

to include the nulls

sql

Problem with Date Function ?

I insert date into table using getdate() function and that works fine.

Now from my query when I try to compare with it and returns nothing.

Thats how the date is stored in the table : 6/4/2007 1:46:57 PM

Now If I do

SELECT * FROM Sometable

WHERE date = getdate() ///Nothng returned

SELECT * FROM sometable

WHERE date = '6-4-2007' // Nothing is returned

How to Fix this issue ?

This has to do with the way that datetime data is stored. Try this:

Code Snippet

select * from someTable

where date >= convert(datetime(convert(varchar(10), getdate(), 101)))

and date < convert(datetime(convert(varchar(10), getdate()+1, 101)))

or

Code Snippet

select * from someTable

where date >= '6-4-2007'

and date < convert(datetime, '6-4-2007')+1

The problem with using the BETWEEN operator is that it includes events that occur exactly on midnight which is technically part of the next day. There are a number of other ways of doing this in addition to these two.

Something that you ought to avoid is loading either GETDATE() or your date constant '6-4-2007' into a variable. Loading this data into a variable eliminates some optimization options that might be exploited by the SQL optimizer.

|||

Hello Kent,

That will return me all dates greater than that specific date. I want the exact date, is there any way to change how the date is stored ? As i'm creating my SQL programatically, it is hard to have everything done at that level.

|||

Sorry my bad, I made a mistake in dates. That works !!!

Is there any way to fix this at Database Level....

|||

Harsimrat,

Datatime data type can include also time part, so if you need to select rows you will have to specify the time part you want.

select *

from sometable

where date >= '20070604' and date < '20070605'

-- or

select *

from sometable

where date between '2007-06-04T10:00:00' and '2007-06-04T18:59:59.997'

If you used function getdate() to insert the rows, then remember that this function returns current date and time and it will not be useful to match rows inserted in the past unless both operations are executed in the same time.

-- this could work

declare @.t table (c1 datetime)

insert into @.t values(getdate())

select * from @.t where c1 = getdate()

-- but not this

declare @.t table (c1 datetime)

insert into @.t values(getdate())

waitfor delay '00:00:00.003'

select * from @.t where c1 = getdate()

AMB

|||

Adding to Kent's explanition, when you store a datetime value as you have, then you will not get a match unless the criteria has the EXACT same datetime value -up to the milliseconds.

So to find all values for a particular date, you need to look for values since midnight, and up to the next midnight. Kent provided one set of criteria that accomplishes that goal, here is another.

--For today

Code Snippet

WHERE ( [Date] >= dateadd( day, datediff( day, 0, getdate() ), 0 )
AND [Date] < dateadd( day, datediff( day, 0, getdate() + 1 ), 0)

)

p.s., You really shouldn't name your columns and tables with 'RESERVED WORDS'. [Date] is a reserved word that has special meaning in SQL Server. Refer to Books Online, Topic: 'Reserved Words'

|||

When I run this query:

Code Snippet

declare @.someTable table
( date datetime
)
insert into @.someTable
select '6/3/7 15:00' union all
select '6/3/7 23:59:59.997' union all
select '6/4/7' union all
select '6/4/7 8:00' union all
select '6/4/7 23:59:59.997' union all
select '6/5/7'
--select * from @.someTable

select * from @.someTable
where date >= convert(datetime, (convert(varchar(10), getdate(), 101)))
and date < convert(datetime, (convert(varchar(10), getdate()+1, 101)))

I get this result:

Code Snippet

date
--
2007-06-04 00:00:00.000
2007-06-04 08:00:00.000
2007-06-04 23:59:59.997

Is that result incorrect?