Query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Query

Author

Topic

vicki
Posting Yak Master

117 Posts

Posted - 2002-03-14 : 15:40:23

Hi,
It's so weried for me, because It been couple hours now and can't figured out why

Query 1:
Select Distinct Title, Forminstances.FormID, convert(char(14), reviewdate,1)
From Forms,UserForms,Users,FormInstances
Where Forms.Formid=UserForms.Formid
And FormInstances.Formid=Forms.Formid
and Userforms.Auth_User = Users.Auth_user
AND FormInstances.PatientFacility = 'IRVING'
AND ReviewDate >= '2/1/2002'
AND ReviewDate <= '2/28/2002'
Order by convert(char(14), reviewdate,1)

Query 2
Select Distinct Title, Forminstances.FormID, convert(char(14), reviewdate,1)
From Forms,UserForms,Users,FormInstances
Where Forms.Formid=UserForms.Formid
And FormInstances.Formid=Forms.Formid
and Userforms.Auth_User = Users.Auth_user
AND FormInstances.PatientFacility = 'IRVING'
AND ReviewDate > '2/1/2002'
AND ReviewDate <= '3/1/2002'
Order by convert(char(14), reviewdate,1)

This query was not display records on 2/28/2002. However if I ran the query2 then the records on 2/28/2002 was display

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 15:48:35

If ReviewDate also includes the time, then:

'2/28/2002' ==> '2/28/2002 12:00:00 AM'

Therefore it won't pull any rows from Feb 28 at 6:00 AM, for example (that's why the next day, March 1, works, because it's using midnight of that day) Try this:

Select Distinct Title, Forminstances.FormID, convert(char(14), reviewdate,1)
From Forms,UserForms,Users,FormInstances
Where Forms.Formid=UserForms.Formid
And FormInstances.Formid=Forms.Formid
and Userforms.Auth_User = Users.Auth_user
AND FormInstances.PatientFacility = 'IRVING'
AND ReviewDate >= '2/1/2002'
AND ReviewDate <= '2/28/2002' + ' 23:59:59'
Order by convert(char(14), reviewdate,1)

I put the section in red that way, so you can pass a time-less date parameter; it will add the timestamp for 11:59:59 PM for that date value.

vicki
Posting Yak Master

117 Posts

Posted - 2002-03-14 : 16:00:37

Thanks alot,

I try your and it work, however how do I pass the time to have the ' 23:59:59'?? AND ReviewDate <= DateFormat(ReviewDate, "mmm-dd-yyyy") +????

thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 16:16:31

I'm not sure I follow you, but you DON'T pass the time to the procedure, just the date value. The query/procedure will append the timestamp to the date value.

If you DO want to restrict the query to date AND time, then you need to use your original query. My solution simply adjusted for a date-only value, because SQL Server defaults these to a midnight time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources