[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Select on InStr or mid(

Posted on 2009-04-23
10
Medium Priority
?
390 Views
Last Modified: 2012-05-06
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?
0
Comment
Question by:Skip_Laughlin
  • 4
  • 3
  • 3
10 Comments
 
LVL 5

Expert Comment

by:allmer
ID: 24217534
try to use datepart

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

Does this function work in your environment?
0
 
LVL 5

Expert Comment

by:allmer
ID: 24217544
0
 

Author Comment

by:Skip_Laughlin
ID: 24217575
I should have said that I need to refer to the extracted seconds later in code.  

If (referenced seconds) <> "last saved value" then ...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Expert Comment

by:reb73
ID: 24217793
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 * FROM
         (SELECT *,RIGHT('0' + <TimeField>, 11) AS TimeValue
           FROM...
           WHERE ...
         ) as Tmp
ORDER BY SUBSTRING(TimeValue,7,2) + LEFT(TimeValue, 2) + SUBSTRING(TimeValue, 5, 2)





0
 

Author Comment

by:Skip_Laughlin
ID: 24217839
Yes, but the field is used and evaulated in many other places, so I am against modifying it in any way.
0
 
LVL 5

Expert Comment

by:allmer
ID: 24217880
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:
Result
sec | min

of course you could be creative and extend the result set.
0
 
LVL 25

Expert Comment

by:reb73
ID: 24218139
"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..
0
 

Author Comment

by:Skip_Laughlin
ID: 24220723
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.
0
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 24220950
<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

0
 

Author Comment

by:Skip_Laughlin
ID: 24224230
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question