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 2005 Forums
 Transact-SQL (2005)
 Flagging shifts that cross midnight

Author  Topic 

MichelleMabbs
Starting Member

24 Posts

Posted - 2015-03-18 : 09:13:11
hi, I have a series of shifts and I wish to flag those that cross midnight as NIGHT shifts and the rest as DAY

example shifts:

10:00:00-22:00:00 DAY
08:00:00-20:00:00 DAY
14:00:00 - 02:00:00 NIGHT

Any ideas would be great thanks


Michelle

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-03-18 : 10:11:13
This example will regard everything that falls between 00:00 and 02:00 as night, all else day.


SELECT
CASE WHEN LEFT(CONVERT(TIME,GETDATE()),5) >= CONVERT(TIME,'00:00')
AND LEFT(CONVERT(TIME,GETDATE()),5) <= CONVERT(TIME,'02:00')
THEN 'Night' ELSE 'Day'
END


We are the creators of our own reality!
Go to Top of Page

MichelleMabbs
Starting Member

24 Posts

Posted - 2015-03-18 : 11:13:10
Hi Thank you that is great

I should have speculated that there are many night shifts not just that specific one for example 19:00:00 to 06:00:00 this example classes that shift as day

Thanks

Michelle
Go to Top of Page

mhorseman
Starting Member

44 Posts

Posted - 2015-03-18 : 11:26:46
Does this cover it? Assumes your columns are called From and To, and that they are Datetime.

SELECT from, to, case when from > to then 'Night' else 'Day' end
from
yourtable


Mark
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-03-18 : 12:15:51
You just need to append to the existing case statement to include other time frames.

In your example of 19:00:00 to 06:00:00 this will appear as Day as the Case only regards Night between 00:00 and 02:00 currently because of the AND clause.

The logic is there to elaborate.


We are the creators of our own reality!
Go to Top of Page
   

- Advertisement -