SELECT range of date in Text field

Question

Can anyone help me in MySQL. I would like to select only date from 9/28/2015 to 10/4/2015.

Please take note, this date is in Text field.

Thank you.

Use date datatype, not text... – jarlh Oct 16 '15 at 07:06 — jarlh, Oct 16 '15 at 07:06
Is your date stored as varchar or as date? – Jens Oct 16 '15 at 07:07 — Jens, Oct 16 '15 at 07:07
Currently it is in Text, i can't change the datatype – Alex Loh Oct 16 '15 at 07:08 — Alex Loh, Oct 16 '15 at 07:08
Yes. Don't use a string data type – Strawberry Oct 16 '15 at 07:12 — Strawberry, Oct 16 '15 at 07:12

score 3 · Accepted Answer · edited Oct 16 '15 at 07:09

3

you can use STR_TO_DATE(yourdatefield, '%m/%d/%Y') to convert text to date and you can later use between clause to restrict output data.

edited Oct 16 '15 at 07:09

Jens

67,715
15
98
113

answered Oct 16 '15 at 07:09

PK20

1,066
8
19

EDIT: Refer: http://stackoverflow.com/questions/1908394/mysql-using-a-string-column-with-date-text-as-a-date-field – PK20 Oct 16 '15 at 07:10
Thank you @PK20 your refer really helpful and i manage to get the answer. SELECT `Date` as ConvDate FROM `table` WHERE STR_TO_DATE(`Date`, '%c/%e/%Y') >= STR_TO_DATE('9/28/2015', '%c/%e/%Y') AND STR_TO_DATE(`Date`, '%c/%e/%Y') <= STR_TO_DATE('10/4/2015', '%c/%e/%Y') GROUP BY date(STR_TO_DATE(Date, '%c/%e/%Y')) ORDER BY date(STR_TO_DATE(Date, '%c/%e/%Y')) – Alex Loh Oct 16 '15 at 07:27

Rahul Tripathi · Answer 2 · 2015-10-16T07:32:44.560

0

You can try like this:

WHERE `Date` BETWEEN CAST('9/28/2015' AS DATE) AND CAST('10/4/2015' AS DATE)

or

WHERE STR_TO_DATE(`Date`, '%m/%d/%Y') BETWEEN STR_TO_DATE('9/28/2015', '%m/%d/%Y') AND STR_TO_DATE('10/4/2015', '%m/%d/%Y')

DEMO

Also try to avoid storing dates as Text. Instead use Date datatype to store dates.

edited Oct 16 '15 at 07:32

answered Oct 16 '15 at 07:08

Rahul Tripathi

168,305
31
280
331

MySQL returned an empty result set (i.e. zero rows) – Alex Loh Oct 16 '15 at 07:09
@AlexLoh:- Updated my answer. Please check now. – Rahul Tripathi Oct 16 '15 at 07:11
Showing rows 0 - 0 (1 total, Query took 0.0047 seconds.) Date NULL – Alex Loh Oct 16 '15 at 07:18
@AlexLoh:- Try this: `select * from test WHERE STR_TO_DATE(`Date`, '%m/%d/%Y') BETWEEN STR_TO_DATE('9/28/2015', '%m/%d/%Y') AND STR_TO_DATE('10/4/2015', '%m/%d/%Y')` Working Demo: http://sqlfiddle.com/#!9/fc70cc/3 – Rahul Tripathi Oct 16 '15 at 07:30
already found my answer SELECT `Date` as ConvDate FROM `table` WHERE STR_TO_DATE(`Date`, '%c/%e/%Y') >= STR_TO_DATE('9/28/2015', '%c/%e/%Y') AND STR_TO_DATE(`Date`, '%c/%e/%Y') <= STR_TO_DATE('10/4/2015', '%c/%e/%Y') GROUP BY date(STR_TO_DATE(Date, '%c/%e/%Y')) ORDER BY date(STR_TO_DATE(Date, '%c/%e/%Y')) Thank you for help :) – Alex Loh Oct 16 '15 at 07:32
@AlexLoh:- Oh..great. Also as recommended. try to avoid storing dates in Text datatype. Just for future prospective. Happy coding. – Rahul Tripathi Oct 16 '15 at 07:33
If i have the choice, i will store it in date datatype. but for this situation, i don't have any choice, it only had to be in Text datatype – Alex Loh Oct 16 '15 at 07:35
@AlexLoh:- You can run an ALTER table command and change the datatype. Actually why I am asking you to do that as it would help you in future as well.(*Not trying to debate or argue just a recommendation*) – Rahul Tripathi Oct 16 '15 at 07:36
1

I understand that you are recommending a best scenario for me, but at this time i can't alter table. Anyway, you kind response is truely helpful for me to solve the issue. Thank you very much again. :) – Alex Loh Oct 16 '15 at 07:39

score 0 · Answer 3 · answered Oct 16 '15 at 07:27

0

Convert first your dates using CONVERT, then use BETWEEN in your WHERE clause.

Try this..

    SELECT * FROM TableName 
    WHERE Date BETWEEN CONVERT(DATE,'9/28/2015') AND CONVERT(DATE,'10/4/2015')

answered Oct 16 '15 at 07:27

japzdivino

1,736
3
17
25

score 0 · Answer 4 · answered Oct 16 '15 at 07:45

0

Try this , in where clause used function str_to_date

SELECT `dateVal`,`id` 
 FROM `datecheck` 
  WHERE STR_TO_DATE(`dateVal`,'%m/%d/%Y') between STR_TO_DATE('9/28/2015',
 '%m/%d/%Y') AND STR_TO_DATE('10/4/2015', '%m/%d/%Y')

answered Oct 16 '15 at 07:45

Akhil S Kamath

1,012
13
23

@Alex Loh hope this solution helps you.No need of converting values in db – Akhil S Kamath Oct 16 '15 at 07:48

score 0 · Answer 5 · answered Oct 31 '17 at 14:51

I had the same type of issue but in my case the date stored also contains the time (also in a text field, for instance 2017-09-11 05:07:58 PM). This is for a wordpress site but I want to query the database directly, the date is in a meta_value. To make this work I ended using a subbstring of the date, i am posting this in case it helps someone:

SELECT ID, display_name, user_email, meta_value FROM bfge_users, bfge_usermeta WHERE (bfge_users.ID = bfge_usermeta.user_id) AND meta_key ='user_login' AND CAST(SUBSTR(meta_value,1,POSITION(' ' IN meta_value)) AS DATE) between '2017-09-11' AND '2017-09-13';

score -1 · Answer 6 · edited May 23 '17 at 12:13

-1

use between, you can read more here:

Select data from date range between two dates

and question is dublicate

edited May 23 '17 at 12:13

Community

1
1

answered Oct 16 '15 at 07:07

Raimondas Kazlauskas

259
2
13

If my datatype is in date, no problem to use between. But my issue now is datatype is in Text – Alex Loh Oct 16 '15 at 07:08
This only works if the date is stored in a date column. It is unclear if the OP stored it in a varchar column – Jens Oct 16 '15 at 07:09

SELECT range of date in Text field

6 Answers6

Linked