``` 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; ```