Select between two dates dateformat

Question

Maybe anyone can help me with this query. I just want to select all rows between two dates, but I'm having problems with the date-format.

My query:

SELECT id, number, date
FROM `table`
WHERE (STR_TO_DATE(date, '%j-%n-%Y') between '6-4-2015' AND '6-4-2016')

I don't know what's wrong with this query. I've tried to use STR_TO_DATE and DATE.

The datetype of date is text (and i want like to keep it)

Here's an example of the database:

1     233        5-4-2015
2     238        6-4-2015
3     431        7-4-2015
4     230        8-4-2015

Can anybody help me?

What's the original format? – Thiago C. S Ventura Apr 29 '15 at 08:35 — Thiago C. S Ventura, Apr 29 '15 at 08:35
show some sample data and what is the datatype of `date` – Abhik Chakraborty Apr 29 '15 at 08:36 — Abhik Chakraborty, Apr 29 '15 at 08:36

score 1 · Answer 1 · answered Apr 29 '15 at 08:45

1

plese try this one strtotime function

'.date("d-m-Y", strtotime($r['date'])).'

For Example

 "select * from sales3 where (sdate between '.date("d-m-Y", strtotime($r['date1'])).' and '.date("d-m-Y", strtotime($r['date2'])).')"

answered Apr 29 '15 at 08:45

Midhun R

11
6

score 1 · Accepted Answer · answered Apr 29 '15 at 08:53

You are doing wrong date conversion in str_to_date , looks like the dates are in d-m-Y format and what you are doing is as

mysql> select STR_TO_DATE('5-4-2015', '%j-%n-%Y') ;
+-------------------------------------+
| STR_TO_DATE('5-4-2015', '%j-%n-%Y') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+

So its giving you null and the store ends there.

You should be using

mysql> select STR_TO_DATE('5-4-2015', '%d-%m-%Y') ;
+-------------------------------------+
| STR_TO_DATE('5-4-2015', '%d-%m-%Y') |
+-------------------------------------+
| 2015-04-05                          |
+-------------------------------------+
1 row in set (0.00 sec)

Now the comparison should be as

WHERE 
STR_TO_DATE(date, '%d-%m-%Y') 
between 
STR_TO_DATE('6-4-2015','%d-%m-%Y') AND STR_TO_DATE('6-4-2015','%d-%m-%Y')

Select between two dates dateformat

2 Answers2