Trouble with previous date code

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

General SQL Server Forums

New to SQL Server Programming

Trouble with previous date code

Author

Topic

c0ldshadow
Starting Member

1 Post

Posted - 2007-10-24 : 14:24:45

I am new to SQL; the following code is supposed find comments that are not null on one day before the studydate.


SELECT	p.PatientID,P.LastName,P.FirstName,P.BirthTime,s.StudyDate,s.Modality,s.Comments
FROM	Studies s INNER JOIN Patients p
ON s.PatientID = p.PatientID
WHERE	s.Comments IS NOT NULL
AND s.StudyDate >= CONVERT(char,DATEADD(day, -12, GETDATE()),101)
AND s.StudyDate < CONVERT(char,DATEADD(day, -11, GETDATE()),101)

Is there a problem with this code? I expect there is because when I ran the query it didn't return any results even though I am positive there are comments in the database one day before the studydates.

Any help would be greatly appreciated

Thanks a ton,
Regards
-avery

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-24 : 14:35:42

You are subtracting 11 and 12 days from today's date. Is that what you intend to do?

What do you see for StudyDate column when you run this:

SELECT p.PatientID,P.LastName,P.FirstName,P.BirthTime,s.StudyDate,s.Modality,s.Comments
FROM Studies s INNER JOIN Patients p
ON s.PatientID = p.PatientID
WHERE s.Comments IS NOT NULL

Also try adding a hard-coded date to your WHERE clause rather than your calculation so that you can determine if the problem is with your data or calculation:

SELECT p.PatientID,P.LastName,P.FirstName,P.BirthTime,s.StudyDate,s.Modality,s.Comments
FROM Studies s INNER JOIN Patients p
ON s.PatientID = p.PatientID
WHERE s.Comments IS NOT NULL AND s.StudyDate >= '10-15-2007' AND s.StudyDate < '10-16-2007'

I just hard-coded 10-15 and 10-16 in the above query. Change the dates as appropriate.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-24 : 14:37:04

Also, you shouldn't be converting to varchar or char in the WHERE clause. You should be doing this instead:

s.StudyDate >= DATEADD(Day, DATEDIFF(Day, 0, GetDate())-12, 0)
AND s.StudyDate < DATEADD(Day, DATEDIFF(Day, 0, GetDate())-11, 0)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources