Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sort date style month.day.year

Posted on 2004-08-04
2
Medium Priority
?
818 Views
Last Modified: 2011-09-20
I am looking for help on sorting dates with mysql. Here is a query that I am working with:
$result = mysql_query("select * from shows ORDER by showdate");

This is working but not as I need it to. Here is a sample date systen I want to use:
month.day.year
03.17.04
08.13.04
11.14.04
01.23.05

How can I set up mysql to work with this date system? Note - I am working with PHPAdmin so if you can speak to me in that lingo it would be helpful because I am not fully versed with mysql command prompt. Also does it matter if I use "/" or "." to separate the month.day.year

Thanks
0
Comment
Question by:waffe
2 Comments
 
LVL 7

Accepted Solution

by:
madwax earned 400 total points
ID: 11721749
Hi waffe,

I would recommend you saving your dates as timestamps since that will give you the most possible functionality. Namely, a timestamp is a integer which is easy to save, and both PHP and MySQL have built-in functions for dealing with timestamps.

E.g.

1. then you can php to output your timestamp as: <?=date("m.d.y",$timestampFromDB?> where you may change the "m.d.y" to whatever you want by to get _ANY_ output. See: http://se.php.net/manual/en/function.date.php

2. MySQL has the built-in function FROM_UNIXTIME() which you can use as well: See:
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

Feel free to ask more if you don't belive me... :)

Regards,
//madwax


0
 
LVL 17

Assisted Solution

by:akshah123
akshah123 earned 200 total points
ID: 11721755
Instead of
$result = mysql_query("select * from shows ORDER by showdate");

try

$result = mysql_query("select * from shows ORDER BY DATE_FORMAT(showdate, '%m/%d/%Y')");
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question