Time stored as varchar

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have time values that are stored in sql server as a varchar tha look like
this:
10:00:00 AM or 9:00:00 AM

I got the sort to work on am and pm but when I run this query in access
against the sql server table:

SELECT time from schedule
where time is not null
order by right(time, 2), mid(time,1,5);

it starts at 10:00:00 AM not 9:00:00 AM

How can I get the times to sort right?
 
Jim

Characters are ... characters. If you aren't working with numbers (things
you add/subtract), the sort is working just the way you told it to ("1xxxx"
comes before "9xxxx").

Good luck

Jeff Boyce
<Access MVP>
 
I have time values that are stored in sql server as a varchar tha look like
this:
10:00:00 AM or 9:00:00 AM

I got the sort to work on am and pm but when I run this query in access
against the sql server table:

SELECT time from schedule
where time is not null
order by right(time, 2), mid(time,1,5);

it starts at 10:00:00 AM not 9:00:00 AM

How can I get the times to sort right?
Click to expand...

Try

Order By CDate([time])

to convert the time to a Date/Time field (which will sort
chronologically).

John W. Vinson[MVP]
 
Back
Top