SQLite Forum

SQL query help for between times and grouping times
Login
This fragment correctly calculates the 24-hour hour offset from the 'h:mm:ss aM' string in `tp`.  The problem is that 12 AM and 12 PM must be handled specially.

```
substr(tp, 1, instr(tp, ':') - 1) + iif(instr(tp, 'PM') > 1 and substr(tp,1,2) != '12', 12, iif(instr(tp, 'AM') > 1 and substr(tp, 1, 2) == '12', -12, 0))
```

You can also use

```
trim(substr(tp, 1, 2), ':') + iif(instr(tp, 'PM') > 1 and substr(tp,1,2) != '12', 12, iif(instr(tp, 'AM') > 1 and substr(tp, 1, 2) == '12', -12, 0))
```

This is, if the time is 'PM' and the hour is NOT 12, then add 12 to the hour, else if the time is 'AM' and the hour is 12 then subtract 12 from the hour, otherwise leave the hour alone (it is between 1 and 11 in the AM and does not need adjustment).