There are a lot of examples, but none have my exact specs. I get a time from a csv file in the form 7:40 AM as a string. No leading 0. MySql works SELECT STR_TO_Date("7:40 PM", "%h:%i %p") AS StartTime returns 19:40:00 I need this for the Order By clause

How can I do this in Sqlite?

CodePudding user response：

Sqlite unfortunately does not provide any neat in-built solutions to this issue. Indeed, there are no provisions to cater for AM/PM in time strings. However, there are work arounds you can use, although it does involve coverting your time string into format that can be manipulated. Firstly, you would have to pad any string with an hour less than 10 with a leading zero. This is easy enough using an iif statement:

"SELECT iif(length(TimesString) =  7, '0' || TimesString, TimesString) from table"

Next you would need to adjust the time string for AM/PM. This issue is well covered in this answer:How to order by date/time with AM/PM in SQLITE. But in a nut shell, it recommends ordering by am/pm first with a case statement and then ordering by time:

ORDER BY (case when PaddedTime like '% am' then 1 else 2 end), PaddedTime

So your full statement would look something like:

SELECT TimeString, iif(length(TimesString) =  7, '0' || TimesString, TimesString) AS PaddedTime from table ORDER BY (case when PaddedTime like '% am' then 1 else 2 end), PaddedTime

This would however fail in the instance where the hour is 12 AM. Here you would have to add an extra iif statement to convert the '12' to '00'. I'll let you have a go at trying that but if you have trouble let me know and I'll help you