midnight - 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 2008 Forums

Transact-SQL (2008)

midnight

Author

Topic

mole999
Starting Member

49 Posts

Posted - 2014-10-02 : 06:18:43

I have a database created externally that stores times without date (varchar(5)) If times occur on a day I have no problem but when midnight crops up I am stumbling

Start = RTC.shift_times = 19:00
End = RTC.shift_times_2 = 07:00
Damage Time = RTC.time_1 = 06:00

I'm error trapping also for missing field values


 case when RTC.time_1 = '' then null else
 case when RTC.shift_times = '' then null else case 
 when cast(RTC.time_1 as datetime) > cast(RTC.shift_times as datetime) then datediff(hour, cast(RTC.shift_times as datetime), cast(RTC.time_1 as datetime))
 else datediff(hour, cast(RTC.shift_times_2 as datetime), cast(RTC.time_1 as datetime)) 
 end end end As Hours_OnDuty2

as rounded hours I'm returning minus one (which is what I expect so far)

my problem is i need to build the length of time between start and finish, i.e. 12 hours then to abstract the minus one and give me a result of 11 hours

guidance gratefully accepted N.B. I've used CAST or CONVERT in my forumlas and get same results, not sure which would be best for this problem

Mole

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-02 : 13:09:20

I think you can simplify it to just this:


select
RTC.shift_times,
RTC.shift_times_2,
RTC.time_1,
case when RTC.time_1 = '' then null
     when RTC.shift_times = '' then null
     else datediff(hour, cast(RTC.shift_times as datetime), cast(RTC.time_1 as datetime) +
              case when RTC.shift_times > RTC.time_1 then 1 else 0 end)
    end As Hours_OnDuty2
 from (
     select shift_times = '19:00',
         shift_times_2 = '07:00',
         time_1 = '06:00'
 ) as RTC

Edit: added bolding to highlight the new code.

mole999
Starting Member

49 Posts

Posted - 2014-10-02 : 13:20:13

Thank you so much, made it look so simple :)

Mole

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources