select datetime without specifying the time part?

Question

I have a table like this: enter image description here

I want to to do something like this:

select * from stat_tps Where min_date ='2013-06-12'

but the problem as you see the min_date is datetime format, so I have to provide the hh:mm:s part. I was wondering if there is any way that I can apply my query without specifying the hours-minutes and seconds?

thanks..

@jishi, it shouldn't work - the times are part of the evaluation — PinnyM, Jul 12 '13 at 14:38

score 3 · Answer 1 · answered Jul 12 '13 at 14:35

3

select * from stat_tps
Where date(min_date) = '2013-06-12'

But that won't take use of indexes if you have one on the min_date column. Better use

select * from stat_tps
Where min_date >= '2013-06-12'
and min_date < '2013-06-13'

answered Jul 12 '13 at 14:35

juergen d

195,137
36
275
343

1

Not possible [with MySQL](http://stackoverflow.com/questions/10595037/is-it-possible-to-have-function-based-index-in-mysql) – juergen d Jul 12 '13 at 14:41
Good point. Sigh... one more missing feature to add to the list. – PinnyM Jul 12 '13 at 15:53

score 2 · Accepted Answer · answered Jul 12 '13 at 14:35

2

Use the DATE() function:

SELECT * FROM stat_tps WHERE DATE(min_date) ='2013-06-12'

answered Jul 12 '13 at 14:35

PinnyM

34,672
3
70
78

score -1 · Answer 3 · answered Jul 12 '13 at 14:43

-1

You need to specify a range, this will effecitvely use an index.

select * from stat_tps 
Where min_date BETWEEN '2013-06-12' AND DATE_ADD('2013-06-12',INTERVAL 1 DAY)

answered Jul 12 '13 at 14:43

jishi

23,497
6
47
74

score -2 · Answer 4 · answered Jul 12 '13 at 14:35

-2

Use the SUBSTR function to chop off the time.

answered Jul 12 '13 at 14:35

PP.

10,574
7
44
58

`select * from stat_tps Where SUBSTR(min_date,0,10) = '2013-06-12'` – PP. Jul 12 '13 at 14:39
1

I used the query you provided and its working perfect, but I had one problem and I wanted to put it here for others to benefit from it when using your answer... setting the length to zero didnt return anything so instead I set the length to 1 and it returned the desired results. ` select * from stat_tps where substring(min_date,1,10)='2013-06-14' ` – tkyass Jul 12 '13 at 14:48
Yes, you're right, the [MYSQL documentation for SUBSTR](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr) specifies that the first character of the string is at position 1. – PP. Jul 12 '13 at 14:50

select datetime without specifying the time part?

4 Answers4