Date and Time Comparion 2

[gatetec]

I have this query to pull out meeting schedule records based on time comparison.

StartTime and EndTime are in the format of i.e. 2007-04-23 09:00:00.0 and 2007-04-23 08:00:00.0

SELECT
tblActivity.Name AS thename,
tblActivity.StartTime as thetime,
tblActivity.EndTime as theendtime,
tblActivity.AttendingCount as thecount,
tblRoom.Name AS theroom
FROM tblActivity
LEFT JOIN
(jncRoomDetails LEFT JOIN tblRoom
ON jncRoomDetails.RoomID = tblRoom.ID)
ON tblActivity.ID = jncRoomDetails.ActivityID
WHERE DATEPART(yy, tblActivity.EndTime) = DATEPART(yy, GETDATE()) and
DATEPART(mm, tblActivity.EndTime) = DATEPART(mm, GETDATE()) and
DATEPART(dd, tblActivity.EndTime) = DATEPART(dd, GETDATE()) and
GETDATE() < tblActivity.EndTime AND
jncRoomDetails.UserScheduled <>0 AND
tblActivity.ScheduleStatus Not In (1,2,3)
GROUP BY
tblActivity.Name,
tblActivity.StartTime,
tblActivity.EndTime,
tblActivity.AttendingCount,
tblRoom.Name,
tblActivity.ID,
jncRoomDetails.UserScheduled
HAVING
(((tblActivity.StartTime) > ** getdate () for today **
AND ((jncRoomDetails.UserScheduled)<>0))
ORDER BY
tblActivity.StartTime

What I want is to pull out the meetings displayed 1 hour before the actual StartTime of the meeting and end the display at the scheduled meeting EndTime. How do you modify this query?

Thx much

 

[sparkbyte]

How about something like this?

select StartTime = DateAdd(Hour, -1, dateadd(dd, datediff(d, 0, getdate())-1, 0))
select EndTime = DateAdd(Second, -1, dateadd(dd, datediff(d, 0, getdate()), 0))



Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com

 

[gatetec]

ok.. I tried this additional piece:

under where clause:

AND (DATEPART(hh,DATEADD(Hour, -1, getdate())) between DATEPART(hh, tblActivity.StartTime) AND DATEPART(hh, tblActivity.EndTime))

// the meetings displayed 1 hour before the actual StartTime of the meeting and end the display at the scheduled meeting EndTime

but, it is returning not right..
I thought it would work though.

What would be wrong here?

 

[BaneNeo]

quick question did you get rid of this:
GETDATE() < tblActivity.EndTime
Before adding that new code.

Because that may be the problem.

If not what is wrong with the output (too many records, too little) can you pin point what records were excluded/included and what were their date/time relative to the output of GETDATE()

Also you may want to try GETUTCDATE() on a whim to see if this fixes it.

 

[sparkbyte]

I would like to ask. Why are you getting the current date time in your WHERE clause if you are retrieving times of meetings?

WHERE DATEPART(yy, tblActivity.EndTime) = DATEPART(yy, GETDATE()) and
DATEPART(mm, tblActivity.EndTime) = DATEPART(mm, GETDATE()) and
DATEPART(dd, tblActivity.EndTime) = DATEPART(dd, GETDATE()) and
GETDATE() < tblActivity.EndTime AND
jncRoomDetails.UserScheduled <>0 AND
tblActivity.ScheduleStatus Not In (1,2,3)

I am quite new to SQL, but wouldn't this only return meetings one hour prior to the current time of the script running?

Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com

 

[AlexCuse]

Hey whats' with this:

WHERE DATEPART(yy, tblActivity.EndTime) = DATEPART(yy, GETDATE()) and
        DATEPART(mm, tblActivity.EndTime) = DATEPART(mm, GETDATE()) and
        DATEPART(dd, tblActivity.EndTime) = DATEPART(dd, GETDATE()) and

?

If you need to remove a time component, its' much easier to do this:

select dateadd(day, datediff(day, 0, getdate()), 0)

Basically, you take the difference in days between your date field and the date 0 (19000101) and add it to date 0 (so you lose hours, minutes, etc...).

If you have a look at that you should be able to work it into your where clause.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[BaneNeo]

AlexCuse:

The point of
WHERE DATEPART(yy, tblActivity.EndTime) = DATEPART(yy, GETDATE()) and
DATEPART(mm, tblActivity.EndTime) = DATEPART(mm, GETDATE()) and
DATEPART(dd, tblActivity.EndTime) = DATEPART(dd, GETDATE()) and

Is to compare the year, month, and day of the EndTime value with the GetDate() value.

Since there is a time component as well in both of the fetched values of EndTime and GetDate()

I believe the orginal poster could shorten these three lines to this:

WHERE DATEDIFF(dd, GetDate(), tblActivity.EndTime) = '0'

and produce the same result since when GetDate() and EndTime are equal for the yy, mm, and dd values the DateDiff will result in a zero value despite what times are defined by either GetDate() and EndTime since the date part is dd so the time values are discarded.

The use of the embedded DateDiff in the DateAdd function is interesting but I believe it would still result in a time value of 12:00:00:00. The orginal time values would be gone for GetDate() and replaced by the filler time value of noon.

However if this was used for both GetDate() and EndTime it would have the desired result since both of their times would equal eachother.

 

[gatetec]

thx so much for the tips.
It is very useful.