Hi, I have a column "statustime" in a table , which has values( like,10:24:10 PM,12:04:00 AM) with mixed of AM and PM . I want to display the other values by "statustime" in ASC . How to do?
AntonyRayan 15 Posting Whiz in Training
rubberman 1,355 Nearly a Posting Virtuoso Featured Poster
Date and time output formatting can easily be done with a format string in the query. Check the MySQL online documentation for your version for details: www.mysql.com
diafol
You may have better luck with storing times in 24 hour format. Then it takes care of itself.
AntonyRayan 15 Posting Whiz in Training
OK Mr.Diafol, I understood your opinion. Is there any solutions for my problem?
Because, Now changing it into 24 Hrs format is not possible(it has more than 1500 records)
jkon 636 Posting Whiz in Training Featured Poster
AntonyRayan it is possible , just create a new field in the table with the type you like , a batch to copy data appropriately from the old field to the new , and delete the old field (after that you could even rename the new field to have the name the old had)
diafol
As jkon says. You could run a simple query to convert it too. Why 1500 records should nake any diffrrence. Did you assume you.d have to do it manually?
Lauren_2 0 Newbie Poster
I have been working with www.essayhelpdeal.co.uk non-SQL databases for more than 20 years. I chose to update my aptitude set and get a MCDBA cert (Microsoft Certified Data Base Analyst), and it took any longer than I suspected to finish the Database Design test. For your situation, I'd propose that you utilize Microsoft Access as the back end. You can manufacture reports and detach records without learning SQL (altho you can see the SQL they create as you go). At that point it turns into a matter of discovering how to trigger Access activities from the web. That is not my range of aptitude.
cereal 1,524 Nearly a Senior Poster Featured Poster
@Antony try:
SELECT * FROM `TABLENAME` ODER BY date_format(str_to_date(`TIME_COLUMN`, '%r'), '%T') ASC;This will convert the string to a timestamp that MySQL can parse, then use %T to get the order in the 24H format. But, as suggested, it's better to convert the existing rows into the 24H format and then always save in that format, to avoid extra processing. So you could run an update query, just like above:
UPDATE `TABLENAME` SET `TIME_COLUM` = date_format(str_to_date(`TIME_COLUMN`, '%r'), '%T');Replace TABLENAME and TIME_COLUMN with yours and remember to test on a copy.
Rule is: save always in UTC, save always in the format that the database can parse, then convert for the client.
For more info see:
Bye!
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.