Page 1 of 1
How to sort date field with varchar 20 data type?
Posted: Fri May 04, 2007 4:14 pm
by amir
Hello,
I am working on a project and i have to make some changes. I have to display listing like
username | name | DOB
aamir aamir Apr 16, 2007
my problem is that date is being stored in varchar 20 format and i want to make this DOB field link clickable sort ASC and DESC but when I do this, it gives weired results.
So please advise.
Thanks a million.
Posted: Fri May 04, 2007 5:05 pm
by RobertGonzalez
I ran into this in an app I was making as well. When I pulled the data from the database, I created a new appended field called timestamp and set it to the timestamp of that date. When the sort was ran, rather than actually use the date, I used the timestamp as the sort criteria. I never showed the timestamp, just used it for sorting.
Posted: Fri May 04, 2007 5:08 pm
by amir
Thanks for your quick repsonse, I 'll try it.
Kind Regards,
Posted: Fri May 04, 2007 5:16 pm
by Chris Corbyn
Everah wrote:I ran into this in an app I was making as well. When I pulled the data from the database, I created a new appended field called timestamp and set it to the timestamp of that date. When the sort was ran, rather than actually use the date, I used the timestamp as the sort criteria. I never showed the timestamp, just used it for sorting.
You may be able to simply type-cast the column during your retrieve operation. However, it's probably going to be better for you in the long run to convert all those records to use the DATETIME type.
Posted: Fri May 04, 2007 5:34 pm
by amir
d11wtq: thanks for your response
but could you please explain it a bit with example if possible?
You may be able to simply type-cast the column during your retrieve operation
thanks once again =)
Posted: Fri May 04, 2007 6:14 pm
by amir
When I browse from the PHPMYADMIN, I can see the results like
user_regdate
Mar 04, 2007
Nov 21, 2006
Nov 23, 2006
and so on ...
but when I type cast it like
SELECT user_id, username, user_email, CAST(user_regdate AS DATETIME), dob, grade, referal FROM nuke_users WHERE user_level > '0' AND user_id > '1' ORDER BY username LIMIT 0,100
then I get NULL in this field i.e. user_regdate.
Please advise what I am doing wrong and what I have to do?
Thanks a Million =)
Posted: Fri May 04, 2007 6:34 pm
by RobertGonzalez
Try:
Code: Select all
SELECT user_id, username, user_email, CAST(user_regdate AS DATE) AS new_user_regdate, dob, grade, referal
FROM nuke_users
WHERE user_level > '0'
AND user_id > '1'
ORDER BY username LIMIT 0,100;
Posted: Fri May 04, 2007 6:44 pm
by amir
thanks but its giving me NULL in that field e.g.
user_id username user_email new_user_regdate dob grade referal
202 aaaaaa
aaam1r@yahoo.com NULL 0
and I have values in all records for this field
Please Advise.
Posted: Fri May 04, 2007 7:37 pm
by Kieran Huggins
hmmm.. I would add an extra column as Everah suggested, but I would make it a standard MySQL date type. This way you can use all the built in MySQL date/time functions, their much faster if you need to do anything fancy. Besides, then you don't have to modify any existing code, and what's the harm in a few extra bytes per record?
Posted: Fri May 04, 2007 8:03 pm
by nickvd
Kieran Huggins wrote:...what's the harm in a few extra bytes per record?
Depends on how many rows
http://www.google.ca/search?q=3+bytes+times+30+billion
30 billion rows would be 83 gig of extra space

Posted: Sat May 05, 2007 8:35 pm
by Kieran Huggins
what's a mere 83 gigs when you can justify a record set of 30 billion?
Posted: Sat May 05, 2007 10:55 pm
by nickvd
Kieran Huggins wrote:what's a mere 83 gigs when you can justify a record set of 30 billion?
Heh I guess you have a point
