Sorting problem

[Eager_Beever]

I am developing ASP.net 2.0 website with SQL Server as database and C# 2005 as the programming language.

In one of my tables, I have a filed named RTime of char datatype to store times. The values of this particular field are like 09:00 AM, 09:15 AM, 09:30 AM, ------ 11:45 AM, 12:00 PM, 12:15 PM, ---- 12:45 PM, 01:00 PM, 01:15 PM, ---- 08:00 PM.

I had thought that, if I use order by RTime in my select statement, the records will be sorted in above order. But I am getting the records in the format 01:00 PM, 01:15 PM, -----08:00 PM, 09:00 AM, 09:15 AM, --- 12:45 AM. My logic was that the records will be sorted using the whole value, and since A(M) comes before P(M), I will get the proper output.

How can I handle the above problem?

Lalit Kumar Barik
India

[almostbob]

the database is meant to be machine readable
human readable data constructs just *expletive deleted* the data
time is not a character string
time is, int, numeric result from the time functions (seconds from 0.00 1 jan 1970).
sql (php asp perl flex) time functions convert human readable day month year strings to timestamps to be stored in the database
timestamps are faster smaller easier to correctly manipulate
change your database to properly store the 'unix' timestamp and format to human readable in the output script

sql now() time()
php time() mktime() strtotime()
asp time() date()

[Eager_Beever]

I solved the above problem by using cast(RTime as smalldatetime). I got the solution from another forum. The person was patient enough to understand my problem, took the pain to analyse and suggest a solution. I clearly wrote that, I was thinking that the sorting will be done as per the entire string content and had tested under Excel sorting.

Lalit Kumar Barik
India

[almostbob]

Carry your 'solution' to its end
The business expands the web site has multi millions of users
the database is gigbaytes larger than it need be
operations that should take microseconds very much longer
The correct solution is always to use the software as it was designed to be used
not to use a construct to repair another construct
cast(RTime as smalldatetime)
is several orders of magnitude slower than
time()
get the design right, is better than get the wrong design working