Member Avatar for 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

  • 2 Contributors
  • 3 Replies
  • 103 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by almostbob
Member Avatar for 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()

Member Avatar for 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

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.