Case statement with DATEADD for over 24 hour calculation

Question

I'm having a bit of a nightmare with a project at work. I'm pulling information from our rostering system using SQL. For whatever reason, the rostering system allows on call shifts to be created as ‘+0000 - 0700’ for example. This is returned at 24:00 - 31:00.

It's taken me days to identify this fact as the cause for my misery when testing the SQL. As soon as the routine hits one of these shifts it craps out with an ‘out of range error’ which makes much more sense now I've found the problem!

However, since I need to cater for weirdness in my code it's not as simple as telling someone to change their practice so the shifts over 24 hours no longer exist, so I was looking to manually interpret these problem times into something I can work with.

The following line makes sense to me but also fails with an out of range error:

SELECT
    RRP.shiftdate,
    CASE 
       WHEN RDS.SHIFTSTART > ‘23:59:59’ 
          THEN DATEADD(HOUR, -24, RDS.SHIFTSTART)
          ELSE RDS.SHIFTSTART
    END AS CLEANSED_SHIFTSTART
...

However, this crashes and shows this error:

Conversion of a varchar data type to a date time data type resulted in an out of range value

Which part of the function is complaining?

CASE WHEN RDS.SHIFTSTART > ‘23:59:59’ THEN ‘ABOVE 24H’

This works well, but I can't work out the issue with the DATEADD, as I'm using it successfully elsewhere in my code.

Answer 1

I tried to delete this answer, but it doesn't let me as it's been accepted. So I just I'm just editing it to say why it shouldn't be used and what was my mistake, I hope that's also useful:

I suggested using replace, and that works by the value, not the position, so if I had a time that is '28:59:28', it would actually be changed to 04:59:4

The solution that I wrongly suggested was:

This should do the trick, considering that you're using strings:

SELECT CASE WHEN RDS.SHIFTSTART > '23:59:59' THEN 
                    CASE WHEN (CAST(LEFT(RDS.SHIFTSTART,2) AS INT)%24) < 10
                         THEN '0'+ REPLACE(RDS.SHIFTSTART,LEFT(RDS.SHIFTSTART,2),(CAST(LEFT(RDS.SHIFTSTART,2) AS INT)%24))
                    ELSE REPLACE(RDS.SHIFTSTART,LEFT(RDS.SHIFTSTART,2), (CAST(LEFT(RDS.SHIFTSTART,2) AS INT)%24) )
                    END
       ELSE RDS.SHIFTSTART
       END AS Cleansed_ShiftStart

This will work for any hours, so if you had a shift starting at: '49:34:54' that should be ' 01:34:54'

But if the shifts will be, at the most, +7, then you could simply do:

    SELECT CASE WHEN RDS.SHIFTSTART > '23:59:59' 
                THEN '0'+ REPLACE(RDS.SHIFTSTART,LEFT(RDS.SHIFTSTART,2),(CAST(LEFT(RDS.SHIFTSTART,2) AS INT)%24))                     
                ELSE RDS.SHIFTSTART
           END AS Cleansed_ShiftStart