Date/Time Comparison Problem

[mbeth6]

Hello All.

I have a table that has entries for specific times of the day. I want to compare the current time to that table and process records based on whether the current time is between the min and max times for a day. There can be multiple entries and I am not sure of how to do this. Example:

I want to select the record that falls between 12:05:00 PM and 3:05:00 PM based on the current time.

Any help will be greatly appreciated.

 

[gmmastros]

Can you show some sample data and expected results?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mbeth6]

I am looking for the PrimaryOnCall.

weekDay validTime PrimaryOnCall
Monday 12:05:00 PM John Doe
Monday 3:00:00 PM Jane Doe
Monday 7:05:00 PM Jon Public

The current time is 1:35:00 PM and I need to find out who the PrimaryOnCall is to assign them a repair ticket.

So I would need to test the dates and was thinking a BETWEEN clause but I cannot make it work correctly.

Thank you again

 

[bborissov]

DECLARE @StartDate as datetime
DECLARE @EndDate   as datetime
SET @StartDate = CAST(CONVERT(varchar(10),GETDATE(),102)+' 12:05:00' as datetime)
SET @EndDate = CAST(CONVERT(varchar(10),GETDATE(),102)+' 15:05:00' as datetime)

SELECT ....
FROM ....
WHERE DateTimeField BETWEEN @StartDate AND @EndDate

(not tested well)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

Based on your sample data, John Doe is on call from 12:05 PM until 3:00 PM. Jane Doe is on call from 3:00 PM until 7:05 PM.

Is this right?

Try...

[tt][blue]
Select Top 1 *
From TableName
Where ValidTime < GetDate() - DateDiff(Day, 0, GetDate())
Order By ValidTime DESC
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh. I missed the weekday stuff.

[COLOR=blue]Select[/color] Top 1 *
[COLOR=blue]From[/color]   [!]TableName[/!]
[COLOR=blue]Where[/color]  ValidTime < [COLOR=#FF00FF]GetDate[/color]() - [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], 0, [COLOR=#FF00FF]GetDate[/color]())
       And [COLOR=#FF00FF]Weekday[/color] = [COLOR=#FF00FF]DateName[/color]([COLOR=#FF00FF]Weekday[/color], [COLOR=#FF00FF]GetDate[/color]())
[COLOR=blue]Order[/color] [COLOR=blue]By[/color] ValidTime [COLOR=#FF00FF]DESC[/color]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mbeth6]

George-

Thank you so much. This works great.

 

[gmmastros]

Good. I'm glad. Do you understand it? Seriously, if you have any questions about the code I posted, and would like me to explain it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mbeth6]

George, I actually do understand just could not figure out the correct way to code it. It has been a long time since I did much SQL and am trying to dust the cobwebs off.