SQLite Forum

SQL query help for between times and grouping times
Login
```
create view trafficdatafixed as select *, datetime(substr(EventDate, length(EventDate)-3, 4) || '-' || printf('%02d', substr(EventDate, 1, instr(EventDate, '/') - 1) + 0) || '-' || trim(substr(EventDate, instr(EventDate, '/') + 1, 2), '/') || ' ' || printf('%02d', (substr(EventTime, 1, instr(EventTime,':')-1) + iif(instr(EventTime,'PM') > 1, 12, 0)) % 24) || substr(EventTime,instr(EventTime,':'), 6)) as timestamp from trafficdata;
```

This should work for all common fracked up date and time formats provided that the year is ALWAYS 4 digits and the MINUTES and SECONDS are ALWAYS 2 digits.  (that is, M/D/YYYY for the date and h:mm:ss xM for the time.

Ooops.  If the data format allows single digit days then you need this:

```
create view trafficdatafixed as select *, datetime(substr(EventDate, length(EventDate)-3, 4) || '-' || printf('%02d', substr(EventDate, 1, instr(EventDate, '/') - 1) + 0) || '-' || printf('%02d', trim(substr(EventDate, instr(EventDate, '/') + 1, 2), '/') + 0) || ' ' || printf('%02d', (substr(EventTime, 1, instr(EventTime,':')-1) + iif(instr(EventTime,'PM') > 1, 12, 0)) % 24) || substr(EventTime,instr(EventTime,':'), 6)) as timestamp from trafficdata;
```