How to sort date field with varchar 20 data type?
Moderator: General Moderators
How to sort date field with varchar 20 data type?
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.
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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
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.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.
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 =)
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 =)
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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;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.
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.
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
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?
-
nickvd
- DevNet Resident
- Posts: 1027
- Joined: Thu Mar 10, 2005 5:27 pm
- Location: Southern Ontario
- Contact:
Depends on how many rowsKieran Huggins wrote:...what's the harm in a few extra bytes per record?
http://www.google.ca/search?q=3+bytes+times+30+billion
30 billion rows would be 83 gig of extra space
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact: