Link to home
Start Free TrialLog in
Avatar of jrepdev
jrepdev

asked on

'Simple' SQL Problem selecting null dates

Hi,
This really should be simple, but I'm having strange behaviour on the following code:
This works fine (returns 31 rows):
DECLARE @numInstances int
EXEC      @numInstances = sp_executesql N'SELECT     COUNT(*) AS numInstances
FROM         xjrvw_TR_Instances
WHERE     (fkResponsiblePersonId = @ResponsiblePersonId) AND (fkOrganizationId = @OrganizationId) AND (TraceDate IS
NULL)',N'@ResponsiblePersonId int,@OrganizationId int',@ResponsiblePersonId=1,@OrganizationId=2

This returns 0 rows:
DECLARE @numInstances int
EXEC      @numInstances = sp_executesql N'SELECT     COUNT(*) AS numInstances
FROM         xjrvw_TR_Instances
WHERE     (fkResponsiblePersonId = @ResponsiblePersonId) AND (fkOrganizationId = @OrganizationId) AND (TraceDate IS
NULL)',N'@ResponsiblePersonId int,@OrganizationId int',@ResponsiblePersonId=1,@OrganizationId=2
GO
And this also returns 0 rows (from the SQL Profiler when I run through the app)
DECLARE @numInstances int
EXEC      @numInstances = sp_executesql N'SELECT     COUNT(*) AS numInstances
FROM         xjrvw_TR_Instances
WHERE     (fkResponsiblePersonId = @ResponsiblePersonId) AND (fkOrganizationId = @OrganizationId) AND (TraceDate IS
NULL)',N'@ResponsiblePersonId int,@OrganizationId int',@ResponsiblePersonId=1,@OrganizationId=2
GO

I have tried changing it to a store procedure, to no avail.  The problem is definitely with the TraceDate IS NULL  - it works without that part of the predicate. I tried TraceDate.ISNULL = 1 but it tells me 'Cannot call methods on datetime.'

Can anyone please help?  I need to run it through the app, which interprets the top code to the second version.  

Thanks!
Carol.

exec sp_executesql N'SELECT     COUNT(*) AS numInstances
FROM         xjrvw_TR_Instances
WHERE     (fkResponsiblePersonId = @ResponsiblePersonId) AND (fkOrganizationId = @OrganizationId) AND (TraceDate IS 
NULL)',N'@ResponsiblePersonId int,@OrganizationId int',@ResponsiblePersonId=1,@OrganizationId=2
GO
DECLARE @numInstances int
EXEC	@numInstances = sp_executesql N'SELECT     COUNT(*) AS numInstances
FROM         xjrvw_TR_Instances
WHERE     (fkResponsiblePersonId = @ResponsiblePersonId) AND (fkOrganizationId = @OrganizationId) AND (TraceDate IS 
NULL)',N'@ResponsiblePersonId int,@OrganizationId int',@ResponsiblePersonId=1,@OrganizationId=2
GO
DECLARE @ResponsiblePersonId int
SET @ResponsiblePersonId = 2
DECLARE @OrganizationId int
SET @OrganizationId=1
SELECT     COUNT(*) AS numInstances
FROM         xjrvw_TR_Instances
WHERE     (fkResponsiblePersonId = @ResponsiblePersonId) AND (fkOrganizationId = @OrganizationId) AND (TraceDate IS 
NULL)

Open in new window

Avatar of vinurajr
vinurajr

try with isnull(TraceDate,1)=1 or TraceDate is null
Avatar of jrepdev

ASKER

Thanks.  Nice try, but that didn't work either...  It doesn't fail, just returns 0 rows instead of 31.  I could mention that this is being implemented via an .xsd using C#, not that it will help :-)

I appreciate your help,
Carol.
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jrepdev

ASKER

Actually, my problem was much simpler - I'd transposed the values for the parameters!  However, that was a really interesting post - thank you - I didn't know that about Ansi Nulls.