Query Work Shift - 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 Work Shift

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-20 : 09:19:06

Julio writes "Hi:

I would like to know if anybody have some tips on how to implement this. I
have a table name tblShift with the following fields

intShiftId    dtStartTime                 dtEndTime
1             2/12/2002 7:00:00 AM        2/12/2002 3:00:00 PM
2             2/12/2002 3:00:00 PM        2/12/2002 11:00:00 PM
3             2/12/2002 11:00:00 PM       2/12/2002 7:00:00 AM

I want to create a select statement that base on the current time I will
return what shift it is ?

I have no problem getting 1st and 2nd shift with the following SQL:

select intShiftId from tblShift where
(DatePart(hh,dtStartTime )+ (DatePart(mi,dtStartTime )/60.0)) <=
(DatePart(hh,GetDate()) + (DatePart(mi,GetDate())/60.0))
and
(DatePart(hh,dtEndTime) + (DatePart(mi,dtEndTime )/60.0))>
(DatePart(hh,GetDate()) + (DatePart(mi,GetDate())/60.0))

I know why but I cannot come with the right Expression. Do I have a good
approach of creating the Shift table and running an Select against it to
find out the shift ?

Thanks for your help"

izaltsman
A custom title

1139 Posts

Posted - 2002-02-20 : 09:39:07

I think you are making it more difficult than it has to be...
If you want to consider both the date and time you have stored for each shift, your query should look something like this:


select intShiftId 
from tblShift 
where getdate() BETWEEN dtStartTime AND dtEndTime

Or if you only care about the time portion of dtStartTime AND dtEndTime (which seems to be the case based on the SELECT statement you provided) then I believe something like this would work:


select intShiftId 
from tblShift 
where CONVERT(DATETIME, CONVERT(varchar, getdate(), 108), 108) 
		BETWEEN CONVERT(datetime, CONVERT(varchar, dtStartTime, 108), 108) 
		AND     CONVERT(datetime, CONVERT(varchar, dtEndTime, 108), 108)

Edited by - izaltsman on 02/20/2002 09:40:37