test distinct datetime field - sql

[PhilgB]

My db has a datetime field. My query needs to return all the records within a certain month distinct per day. The month part I have, however limiting the DISTINCT to day only I cant find. All I have to do is change the format of the fields that its testing.

From what ive read, its something like...
SELECT DATE(timeStart) FROM...
or
SELECT DATEFORMAT(timeStart,something) FROM...

Any help would be great, thanks!

[PhilgB]

Because the DateTime field holds, well.. the date AND the time, using DISTINCT in my sql statement returns all the records within that month. DISTINCT needs to test only the date part of the datetime field.

Ex: db has...
id dtField
0 02/12/2003 and time
1 02/16/2003 and time
2 02/27/2003 and time
3 02/27/2003 and time

The last two records have the same year, month and day with different times. So, sql returns four records instead of three.

How can i test only the date part of the dtField...

Thanks again

[Ribeyed]

hi,
not sure if this is what you are trying to do:


sql = "SELECT COUNT(*) FROM table WHERE " & _
"YEAR(dateColumn) = " & year(date()) & _
" AND MONTH(dateColumn) = " & month(date())


Hope this helps

[PhilgB]

It still isnt working...
Ive seen dozens of functions but none of them do anything.. all i get is errors...

[PhilgB]

Ill ask my teach... ill post later

[PhilgB]

Ok! The datetime fields is actualy a double type. Whole being date, decimal being time. Using the INT() function returns the integer part, ocaisionaly returning one day more. But Im closer than before.

Thanks DC.

SELECT DISTINCT INT(timeStart) FROM...

[PhilgB]

It wasnt retruning additional days before, but I had been warned of that possibility. I havent noticed any difference but it will probably save me from some headaches later, thanks!

[celebguy_dv]

http://banners.dollarmachine.com/pic/2014000/hal001.gif (http://www.kinkyceleb.com/1261795520)

[celebguy_dv]

http://banners.dollarmachine.com/pic/2014000/hal001.gif (http://www.kinkyceleb.com/1261795520)