[SOLVED] Selecting DISTINCT date from a DATETIME

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

[SOLVED] Selecting DISTINCT date from a DATETIME

Post by Mr Tech »

I have a DATETIME row in my MySQL database table... I want to use the DISTINCT function to select just the date out of the DATETIME... I tried this code but it didn't work

Code: Select all

SELECT DISTINCT(DATE(time)) FROM appointments
Is there another way to do this that actually works?
Last edited by Mr Tech on Tue Jan 20, 2009 6:05 am, edited 1 time in total.
jerominejuliana
Forum Newbie
Posts: 2
Joined: Tue Jan 20, 2009 3:19 am

Re: Selecting DISTINCT date from a DATETIME

Post by jerominejuliana »

d -> date

1. 2006-12-19 09:45:00
2. 2006-12-19 05:45:00
3. 2006-12-20 05:45:00
4. 2006-12-21 05:45:00

Note that id 1 and 2 have the same date.

I want with a sql question get this result:

1. 2006-12-19
3. 2006-12-20
4. 2006-12-21

http://www.searchengineoptimizationservices.us
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Re: Selecting DISTINCT date from a DATETIME

Post by Mr Tech »

I solved my own issue. Woohoo! Turns out my code was right except it needed to have little addition...

Code: Select all

SELECT DISTINCT(DATE(time)) AS time FROM appointments
I figured it wasn't working because it wasn;t returning any results. Adding the AS time to the query seems to have fixed it :)
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: [SOLVED] Selecting DISTINCT date from a DATETIME

Post by Sindarin »

Wouldn't explode() have worked as well?
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Re: [SOLVED] Selecting DISTINCT date from a DATETIME

Post by Mr Tech »

Is explode() a function you can use in the MySQL Query or is it just a PHP function? I'd prefer to do it within the MySQL Query so there is less code.
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: [SOLVED] Selecting DISTINCT date from a DATETIME

Post by Sindarin »

It's a php one, explode can break a string into parts using a delimiter: http://gr.php.net/explode
The SQL query will be good for your case.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: [SOLVED] Selecting DISTINCT date from a DATETIME

Post by VladSun »

Sindarin wrote:Wouldn't explode() have worked as well?
If you do this, you will have to implement the DISTINCT functionality at PHP code layer, which I think will be heavy and inefficient way to do it.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply