Page 1 of 1

[SOLVED] Selecting DISTINCT date from a DATETIME

Posted: Tue Jan 20, 2009 12:40 am
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?

Re: Selecting DISTINCT date from a DATETIME

Posted: Tue Jan 20, 2009 3:36 am
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

Re: Selecting DISTINCT date from a DATETIME

Posted: Tue Jan 20, 2009 6:05 am
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 :)

Re: [SOLVED] Selecting DISTINCT date from a DATETIME

Posted: Tue Jan 20, 2009 8:28 am
by Sindarin
Wouldn't explode() have worked as well?

Re: [SOLVED] Selecting DISTINCT date from a DATETIME

Posted: Tue Jan 20, 2009 5:14 pm
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.

Re: [SOLVED] Selecting DISTINCT date from a DATETIME

Posted: Wed Jan 21, 2009 3:49 am
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.

Re: [SOLVED] Selecting DISTINCT date from a DATETIME

Posted: Wed Jan 21, 2009 6:05 am
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.