Instr Question

[Nilgni]

I am trying to sort on part of a combined field.

Novice at SQL and none of the examples I can pull up seem to solve my issue.


Here is an example of the type of information that I am searching:

Reasoner, Jim 4/25/2006 12:17:23 PM

I want to sort by date and then time with the newest records first.

Here is the code that I am working on (in a Dreamweaver MX query):

SELECT *
FROM tbl_Contact_Information
WHERE [Address Confirmed] = 'confirmed'
ORDER BY RIGHT(Updated_By,INSTR(INSTR(Updated_By," ")+1,Updated_By," ")) DESC

The result should sort by the date so far. I was going to work on the time after I got this to work. If I take out the INSTR argument and place a number, the RIGHT function sorts; so I know that it is the INSTR that is the problem. When I try to execute, I get a 'too few paramaters' error.

I was able to get it to work in MS Excel (don't laugh) and thought that I could transfer it to SQL.

Is there and error in my syntax, or am I trying to do something where nested questions cannot be answered?

Thanks for your help,
Keith

 

[Nilgni]

Okay, I admit that it just didn't occur to me to search for INSTR post before I wrote the above. I was thinking that I was trying to be too complicated.

When I searched, here is the answer I found:

Double quotes need to be single quotes!

Live and learn - Search BEFORE you post

Keith

 

[Tarwn]

Unfortunatly your probably a few minutes from running into an isue where you will realize that since your sorting bya date in a string, it is going to sort that data alphanumerically rather than by date. Generally it's a bad idea to store dates in varchar/text fields because they are then useles as dates without a lotof extra work. Your going to have to convert those values your parsing out into dates before ordering them.