0

how to search between two date , when date format in database like : 

2015-10-10 02:23:41 am

i just want to search between two date with format :

2015-10-10

without 

02:23:41 am

any ideas please ?

  • 3
    define *"not working"* – Funk Forty Niner Dec 31 '15 at 20:18
  • 2
    What format has fiel date? – Yurich Dec 31 '15 at 20:22
  • 5
    What is the `02` at the end of your date: `'2015-09-10 02'` – juergen d Dec 31 '15 at 20:24
  • 3
    seriously doubting OP's going to respond to any of these comments and expecting a magical answer to appear below. Not going to happen; not with me anyway. – Funk Forty Niner Dec 31 '15 at 20:27
  • 1
    With respect, sometimes it's hard to know how to ask the right question. – O. Jones Dec 31 '15 at 20:52
  • I think we also need to motivate the junior programmers as well otherwise critise is very easy..... just my opinion... thinking of senior programmers always big than to juniors... well HAPPY NEW YEAR ALL – devpro Dec 31 '15 at 21:07
  • do you mean they are stored as a string (CHAR/VARCHAR)? – the_nuts Dec 31 '15 at 21:07
  • thank you guys i think i have not asking right question , by way or other that thing what i want to asking about how can i do query for search between two date if column type is ( varchar ) and date format like : 2015-10-10 02:23:41 am so if i have 5 records in database , 2015-10-10 02:23:41 am , 2015-10-10 02:23:41 am , 2015-10-10 02:23:41 am ,2015-10-10 02:23:41 am ,2015-10-10 02:23:41 am and i want to search with this format 2015-10-10 how can i do this ? –  Dec 31 '15 at 21:46

3 Answers3

1

Your question isn't completely clear. But, I guess you hope to find all the rows in your table where Date occurs on or after midnight on 2015-08-05 and before midnight on 2015-09-11 (the day after the end of the range you gave in your question.

Those two search criteria will find all the rows with Date values in the range you specified. (I'm ignoring the 02 at the end of 2015-09-10 02 in your question because I can't guess what it means, if anything.)

Try this query:

  SELECT * 
    FROM table
   WHERE `Date` >= '2015-08-05'
     AND `Date` <  '2015-09-10' + INTERVAL 1 DAY

This has the benefit that it can exploit an index on the Date column if you have one, so it can be fast.

You could write

  SELECT * 
    FROM table            /* slow! */
   WHERE DATE(`Date`) BETWEEN '2015-08-05' AND '2015-09-10'

That's slightly easier to read, but the WHERE condition isn't sargable, so the query will be slower.

Notice that the beginning of the range uses >= -- on or after midnight, and the end of the range uses < -- before midnight.

Pro tip: Avoid the use of reserved words like DATE for column names. If you make mistakes writing your queries, their presence can really confuse MySQL, not to mention you, and slow you down.

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
0

May I suggest:

select * from table where cast(date as date) between '2015-08-05' and '2015-09-10'

When your where clause is based on a timestamp, but you're using date as the parameters for your between, it excludes anything that happens on the second date unless it happened precisely at midnight.

P. Gearman
  • 1,138
  • 11
  • 14
0

When using the end date for the range, include the time of the end of the day:

SELECT *
FROM YourTable
WHERE date BETWEEN '2015-08-05' AND '2015-09-10 23:59:59'
Barmar
  • 741,623
  • 53
  • 500
  • 612