 |  |  |  |  |  |  |  |
Sorting problem
Please support our MS SQL advertiser: Intel Parallel Studio Home
Thread Solved |
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
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
•
•
Join Date: Jan 2009
Posts: 1,382
Reputation: 
Solved Threads: 167
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()
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()
Last edited by almostbob; Mar 9th, 2009 at 4:28 pm.
Failure is not an option It's included free, you don't have to do anything to get it
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it
Please mark solved problems, solved
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it
Please mark solved problems, solved
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
Lalit Kumar Barik
India
•
•
Join Date: Jan 2009
Posts: 1,382
Reputation:
Solved Threads: 167
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
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
Failure is not an option It's included free, you don't have to do anything to get it
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it
Please mark solved problems, solved
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it
Please mark solved problems, solved
 |
Similar Threads
- sorting problem (C++)
- Question: Linear Time Sorting Problem (Computer Science)
- Collapsible table sorting problem (JavaScript / DHTML / AJAX)
- Outlook Mail Sorting (C#)
- Link List Sorting Problem (C++)
- Sorting arrays of pointers with function? (C)
Other Threads in the MS SQL Forum
- Previous Thread: restrict field to alpha only
- Next Thread: I can’t install SQL Server 2008 Express with Advanced Services - Help!
| Thread Tools | Search this Thread |
Latest MS SQL Posts
- Today's Posts
- Unanswered Threads
Related Forum Features
Tag cloud for MS SQL
"last autogrowth business connectingtodatabaseinuse count cursor data database dateadd datepart day" dbsize deadlock delete_trigger getdate highperformancecomputing hpc hpcserver2008 ibm iis loop maximum microsoft ms mssql multiple multithreading news number permission query reporting result server services sets source sql sqlserver sqlserver2005 supercomputing tables uniqueid update view weekday
