SQL Select on InStr or mid(

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?
Who is Participating?
reb73Connect With a Mentor Commented:
<startingtime> should actually be just startingtime, the <> is just an indicator for substitution of actual fieldname.

Try something like -
sql$ =	"SELECT * FROM " & _
	"(SELECT *,RIGHT('0' + startingtime, 11) AS TimeValue " & _
	"FROM tshooter_rotation) AS Tmp " & _
	"ORDER BY concur, rotation, course, " & _
	"SUBSTRING(TimeValue,7,2) + LEFT(TimeValue, 2) + SUBSTRING(TimeValue, 5, 2), " & _
	"startingtime, station, numb"

Open in new window

try to use datepart

DATEPART(second, YOURFIELD) as sec, DATEPART(minute, YOURFIELD) as min
ORDER by sec,min;

Does this function work in your environment?
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Skip_LaughlinAuthor Commented:
I should have said that I need to refer to the extracted seconds later in code.  

If (referenced seconds) <> "last saved value" then ...
I'm presuming the times are stored in a varchar field, is this correct?

If yes, a value of '8:00:06 AM' has a length of 10 characters, while a value of '11:05:07 AM' has a length of 11 characters.

The trick is to prefix this column value with a '0' and use the RIGHT() function to extract the last 11 characters, so the values will end up being '08:00:06 AM' and '11:05:07 AM' respectively.

Now that all values have a fixed length, you can do the order by using specific positions in the string -

         (SELECT *,RIGHT('0' + <TimeField>, 11) AS TimeValue
           WHERE ...
         ) as Tmp
ORDER BY SUBSTRING(TimeValue,7,2) + LEFT(TimeValue, 2) + SUBSTRING(TimeValue, 5, 2)

Skip_LaughlinAuthor Commented:
Yes, but the field is used and evaulated in many other places, so I am against modifying it in any way.
The select above gives you the seconds, minutes, and whatever else you want as atomic pieces in the result table.
You may use them in the code.

For the above only:
sec | min

of course you could be creative and extend the result set.
"so I am against modifying it in any way"

Where is the question of modifying the base field? There are no updates to the base table..The select statement just dynamically reformats the output and sorts based on the reformatting, but the base field is untouched as such..
Skip_LaughlinAuthor Commented:
OK, REB73, tell me what I'm doing wrong.  Here's the string I came up with doing substitutions on your solution:

        sql$ = "SELECT * FROM (SELECT *,RIGHT('0' + <startingtime>, 11) AS TimeValue fROM tshooter_rotation) ORDER BY concur, rotation, course, SUBSTRING(TimeValue,7,2) + LEFT(TimeValue, 2) + SUBSTRING(TimeValue, 5, 2), startingtime, station, numb"""

where "startingtime" is the time field and the table name is tshooter_rotation.  I'm missing something because it returns no records.
Skip_LaughlinAuthor Commented:
Yep, that works.  For Access, I replaced SUBSTRING with MID and it returned the full record set in the order I was seeking.
Thanks for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.