Clicky

My dates are entered in an mysql DB as string in the format MM/DD/YYYY. I can organize the dates with 'ORDER DESC' but now with the change of years the dates are mixing. To be able to order I CONCAT the string and it unmixed but now I can not get the dates in a DESC mode. It will only ORDER ASC. Please help me with my code to get this in DESC.
 SELECT d.*, u.email, u.username , u.gid , u.id AS userid  CONCAT(SUBSTR(cb_dldate1, 7, 4), '-', SUBSTR(cb_dldate1, 1, 2), '-', SUBSTR(cb_dldate1, 4, 2)) as stuff FROM table_D AS d, table_U AS u  WHERE u.id = $user->id AND d.cf_user_id = $user->id  ORDER BY stuff DESC                             
1: 2: 3: 4: 5: 

Select allOpen in new window

asked 01/29/2011 11:54

fun_g's gravatar image

fun_g ♦♦


11 Answers:
Alter your table and add a DATETIME column.  Then write a query to read the currently (incorrectly) formatted dates and convert them to the ISO8601 strings.  Update the table with the new ISO8601 strings, and change your code to use the new column.

Here is an article that tells how to handle dates in PHP and MySQL
http://www.qa.downappz.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Best of luck with it, ~Ray
link

answered

Ray_Paseur's gravatar image

Ray_Paseur

Did you try ORDER BY cb_dldate1 ?
1:
2:
3:
4:
5:
SELECT d.*, u.email, u.username , u.gid , u.id AS userid 
CONCAT(SUBSTR(cb_dldate1, 7, 4), '-', SUBSTR(cb_dldate1, 1, 2), '-', SUBSTR(cb_dldate1, 4, 2)) as stuff
FROM table_D AS d, table_U AS u 
WHERE u.id = $user->id AND d.cf_user_id = $user->id 
ORDER BY cb_dldate1 DESC
link

answered 2011-01-30 at 07:56:50

RajkumarGS's gravatar image

RajkumarGS

Use the mysql date or timestamp field. Then you can just order the way you want, and when you read from it you just use date_format to get it back the way you expected.

SELECT DATE_FORMAT('%m/%d/%Y', db_dldate)

The way you do it now is slow, unreadable and unmaintainable.
link

answered 2011-01-30 at 07:57:39

cyberstalker's gravatar image

cyberstalker

Hello Experts. I am going to take a little time going through this but will get back ASAP. Thank you for your super fast responses.
link

answered 2011-01-30 at 07:59:28

fun_g's gravatar image

fun_g

Agreed. Use a real datetime column and display it how you want instead of a text column to store it how you want it displayed.
link

answered 2011-01-30 at 08:02:15

aarontomosky's gravatar image

aarontomosky

cyberstalker, I agree with your assessment and would love to take this into the UNIX timestamp, but right now all of the entries are in string format.
Use the mysql date or timestamp field. Then you can just order the way you want, and when you read from it you just use date_format to get it back the way you expected.

SELECT DATE_FORMAT('%m/%d/%Y', db_dldate)


RajkumarGS, This is my first failing format. this does not work for the year in a string format. The month and the day will order but not the year.
Did you try ORDER BY cb_dldate1 ?


Ray_Paseur,
This is a great article but I am getting lost in the vast amount of knowledge within. The part I need to know is how to transfer the current date in string format to the new column in the datetime format. Can you supply a some more specific ideas here for this column copy/translation? Do I include the CONCAT function I wrote? Can this transfere be live within the page code or is this a recurring task as new entries are submitted?




link

answered 2011-01-30 at 08:02:20

fun_g's gravatar image

fun_g

Alright. Creating a new column was cake. Now I am stuck on getting my concat sting into the new column. I have been trying such as shown below.  

1:
2:
3:
SELECT CONCAT(SUBSTR(cb_dldate1, 7, 4), '-', SUBSTR(cb_dldate1, 1, 2), '-', SUBSTR(cb_dldate1, 4, 2)) AS `winter` 
FROM `Table_D` 
UPDATE `Table_D` SET `cb_dldate2` = `winter`
link

answered 2011-01-30 at 11:05:30

fun_g's gravatar image

fun_g

I am looking to move away from posting in this method but I need to convert the previously entered string dates into the the DATETIME format.

Can someone direct me to some published methods for a beginner.

Just to clarify, I need to convert an entire column of string format date entrys into datetime format for better data entry.
link

answered 2011-01-30 at 14:31:21

fun_g's gravatar image

fun_g

That's exactly what I posted.
link

answered 2011-02-14 at 10:54:19

aarontomosky's gravatar image

aarontomosky

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

link

answered 2011-02-14 at 11:25:19

angelIII's gravatar image

angelIII

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×81
×1

Asked: 01/29/2011 11:54

Seen: 256 times

Last updated: 12/17/2011 05:22