Date filter in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date filter in stored procedure

Author

Topic

marcuscoker
Starting Member

13 Posts

Posted - 2007-03-08 : 05:59:39

Hi

I was wondering if anybody could help me with this one

I have setup a stored procedure that seems to work ok, however it does not appear to be filtering the dates correctly and i am getting back nulls when i enter some dates that i know should return values

SELECT DISTINCT
dbo.TblEmisDemographics.ExtractID, dbo.TblEmisDemographics.PracticeID, dbo.TblEmisDemographics.PatientID,
dbo.TblEmisDemographics.Surname, dbo.TblEmisDemographics.Forename, dbo.TblEmisDemographics.DOB, dbo.TblEmisDemographics.Sex,
dbo.TblEmisDemographics.HouseNumber, dbo.TblEmisDemographics.HouseName, dbo.TblEmisDemographics.No_Street,
dbo.TblEmisDemographics.Village, dbo.TblEmisDemographics.Town, dbo.TblEmisDemographics.Postcode, dbo.TblEmisDemographics.NHSNumber,
dbo.TblEmisDemographics.RegisteredGP, dbo.TblEmisDemographics.UsualGp, dbo.TblEmisDemographics.RegistrationStatus,
dbo.TblEmisDemographics.DateDeath, dbo.TblEmisDemographics.CombinedID,
dbo.TblEmisDemographics.Deleted
FROM dbo.TblEmisDemographics LEFT OUTER JOIN
dbo.TblEmisImms ON dbo.TblEmisDemographics.CombinedID = dbo.TblEmisImms.CombinedID

WHERE dbo.TblEmisDemographics.PracticeID = @PracticeID and dbo.TblEmisDemographics.extractid = @extractid and (dbo.TblEmisDemographics.Deleted = 0) AND (dbo.TblEmisImms.ID IS NOT NULL) AND (dbo.TblEmisImms.Deleted = 0) AND (dbo.TblEmisDemographics.Surname Like '%'+@Surname+'%' and (DOB >= CONVERT(DATETIME, @Starting, 103) AND DOB <= CONVERT(DATETIME, @Ending, 103)))

order by dbo.TblEmisDemographics.Surname desc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 06:17:24

Why are you involving the TblEmisImms table at all?
No SELECTs are made against this table and since it is LEFT JOIN references, all records from TblEmisDemographics are fetched anyway.

SELECT		d.ExtractID,
		d.PracticeID,
		d.PatientID,
		d.Surname,
		d.Forename,
		d.DOB,
		d.Sex,
		d.HouseNumber,
		d.HouseName,
		d.No_Street,
		d.Village,
		d.Town,
		d.Postcode,
		d.NHSNumber,
		d.RegisteredGP,
		d.UsualGp,
		d.RegistrationStatus,
		d.DateDeath,
		d.CombinedID,
		d.Deleted
FROM		dbo.TblEmisDemographics AS d
WHERE		d.PracticeID = @PracticeID
		AND d.ExtractID = @ExtractID
		AND d.Surname LIKE '%' + @SurName + '%'
		AND d.DOB >= DATEADD(DAY, DATEDIFF(DAY, 0, @Starting), 0)
		AND d.DOB < DATEADD(DAY, DATEDIFF(DAY, 0, @Ending), 1)
		AND d.Deleted = 0

Or try this

SELECT DISTINCT	d.ExtractID,
		d.PracticeID,
		d.PatientID,
		d.Surname,
		d.Forename,
		d.DOB,
		d.Sex,
		d.HouseNumber,
		d.HouseName,
		d.No_Street,
		d.Village,
		d.Town,
		d.Postcode,
		d.NHSNumber,
		d.RegisteredGP,
		d.UsualGp,
		d.RegistrationStatus,
		d.DateDeath,
		d.CombinedID,
		d.Deleted
FROM		dbo.TblEmisDemographics AS d
LEFT JOIN	dbo.TblEmisImms AS i ON i.CombinedID = d.CombinedID AND i.Deleted = 0
WHERE		i.ID IS NOT NULL
		AND d.PracticeID = @PracticeID
		AND d.ExtractID = @ExtractID
		AND d.Surname LIKE '%' + @SurName + '%'
		AND d.DOB >= DATEADD(DAY, DATEDIFF(DAY, 0, @Starting), 0)
		AND d.DOB < DATEADD(DAY, DATEDIFF(DAY, 0, @Ending), 1)
		AND d.Deleted = 0

Peter Larsson
Helsingborg, Sweden

marcuscoker
Starting Member

13 Posts

Posted - 2007-03-08 : 09:07:54

Thanks Peter

That worked perfectly

Marcus

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 09:12:37

Which one?

Peter Larsson
Helsingborg, Sweden

marcuscoker
Starting Member

13 Posts

Posted - 2007-03-08 : 10:17:39

Hi Peter

I thought it was working, using this

SELECT d.ExtractID,
d.PracticeID,
d.PatientID,
d.Surname,
d.Forename,
d.DOB,
d.Sex,
d.HouseNumber,
d.HouseName,
d.No_Street,
d.Village,
d.Town,
d.Postcode,
d.NHSNumber,
d.RegisteredGP,
d.UsualGp,
d.RegistrationStatus,
d.DateDeath,
d.CombinedID,
d.Deleted
FROM dbo.TblEmisDemographics AS d
WHERE d.PracticeID = @PracticeID
AND d.ExtractID = @ExtractID
AND d.Surname LIKE '%' + @SurName + '%'
AND d.DOB >= DATEADD(DAY, DATEDIFF(DAY, 0, @Starting), 0)
AND d.DOB < DATEADD(DAY, DATEDIFF(DAY, 0, @Ending), 1)
AND d.Deleted = 0

But there seems to be a problem with the date. It only appears to work if i post the date in mm/dd/yyyy instead of dd/mm/yyyy

Additionally, how is d.DOB >= DATEADD(DAY, DATEDIFF(DAY, 0, @Starting), 0)
working, i am not familiar with this way of filtering via date

Thanks

Marcus

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-08 : 13:17:33

Marcus,

That date calculation is a way of "removing" the Time portion of the date time. Try running the portions of the date calculation for yourself and you will start to see what is happening:

SELECT GEDATE()
SELECT DATEDIFF(DAY, 0, GETDATE())
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

Here is a link to a good article on SQL DateTimes: [url]http://www.sql-server-performance.com/fk_datetime.asp[/url]

-Ryan

marcuscoker
Starting Member

13 Posts

Posted - 2007-03-09 : 04:53:14

Thanks Ryan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-09 : 04:57:13

Always use proper datatypes for variables and parameters.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources