Query Help - 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

Transact-SQL (2000)

Query Help

Author

Topic

jdelgado
Starting Member

5 Posts

Posted - 2002-02-17 : 23:30:32

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

Nazim
A custom title

1408 Posts

Posted - 2002-02-17 : 23:40:45

Will not this do it

select * from tblShift
where convert(datetime,getdate,112) between convert(datetime,dtStarttime,112) and convert(datetime,dtEndtime,112)

HTH

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

jdelgado
Starting Member

5 Posts

Posted - 2002-02-17 : 23:50:13

Thanks Nazim:

I try this the statement below, but the problem is that if there is a shift that the times crosses a day example 3rd Shift Start at 11:00 PM and finish at 7:00 AM. The sql below works for 1st and 2nd shift but not for 3rd.

select *
from tblshift
where convert(varchar, getdate(),108) between
convert(varchar,dtstarttime,108) and convert(varchar,dtendtime,108)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-18 : 00:01:10

What's with the Time Travel??????

You third row seems to defy the direction of time....

Shouldn't the dtEndTime (3rd Row) be "3/12/2002 7:00:00AM" ?

DavidM

Tomorrow is the same day as Today was the day before.

Nazim
A custom title

1408 Posts

Posted - 2002-02-18 : 00:44:05

Julio this query wouldnt work . coz it takes only the time into consideration irrespective of the Date. the one i wrote should help you.

As David suggested your third row's endtime looks suspicious.

quote:

select *
from tblshift
where convert(varchar, getdate(),108) between
convert(varchar,dtstarttime,108) and convert(varchar,dtendtime,108)

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources