SQL Select on InStr or mid(
Posted on 2009-04-23
I need a SQL select statement to use with VB.
I need to do a select * and order by on a data field that contains times. If the hour of the time is 9 or less, then the string would be something like 8:00:06 AM. If the hour of the time is 10 or greater, then the string would be something like 11:05:07 AM.
I need the records returned to be first sorted by the seconds in the times above and then by the hours and minutes of the string. In some cases, the seconds are the 7th and 8th characters in the string, in the others the seconds are the 6th and 7th characters in the string.
I don't know how to 1) create a temporary field on the fly to use in the query and 2) how to do the IIF on the variable time string.
I know I could add a new field to the table and get this done, but I much prefer not to. I need to do it in code, if possible.
Clear as mud?