How to sort date field with varchar 20 data type?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
amir
Forum Contributor
Posts: 287
Joined: Sat Oct 07, 2006 4:28 pm

How to sort date field with varchar 20 data type?

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
amir
Forum Contributor
Posts: 287
Joined: Sat Oct 07, 2006 4:28 pm

Post by amir »

Thanks for your quick repsonse, I 'll try it.
Kind Regards,
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
amir
Forum Contributor
Posts: 287
Joined: Sat Oct 07, 2006 4:28 pm

Post 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 =)
amir
Forum Contributor
Posts: 287
Joined: Sat Oct 07, 2006 4:28 pm

Post 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 =)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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;
amir
Forum Contributor
Posts: 287
Joined: Sat Oct 07, 2006 4:28 pm

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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?
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post 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 ;)
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

what's a mere 83 gigs when you can justify a record set of 30 billion?
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post 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 :)
Post Reply