Flagging shifts that cross midnight

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!

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

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

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources