Using BETWEEN for dates stored as string in MySQL

Question

The date field in the database tables I am currently working on is in the form of varchar and given in a dd.mm.yyyy format.

For example date : 03.02.2018

There is screen above

So when I send to data with ajax is not working correctly.

There are code below

function clean($data) {
    global $con;
    $data = mysqli_real_escape_string($con, strip_tags($data));
    return $data;
}
$start_date = clean($_POST['start']);
$end_date   = clean($_POST['end']);

if (isset($start_date) && isset($end_date)) {
    $get_date = "SELECT * FROM records WHERE reservations BETWEEN '$start_date' AND '$end_date' ";
}

Start date and End Date are same working for example today or yesterday. But when dates are different dont work for example start date : 03.02.2018 end date :03.03.2018

Best Regards

Thank you.

That's not an actual date-field in the database, is it? MySQL stores dates in the format: YYYY-MM-DD. — M. Eriksson
– M. Eriksson, Commented Aug 3, 2018 at 7:40
You cannot use BETWEEN for varchar column type in MySQL, you need some workaround. — Nikola Kirincic
– Nikola Kirincic, Commented Aug 3, 2018 at 7:42
@Magnus Eriksson yes column not date format it is varchar store date/ MySQL stores dates in format same DD.MM.YYYY — hakkidilek
– hakkidilek, Commented Aug 3, 2018 at 8:30

Jim · Accepted Answer · 2018-08-03 09:40:19Z

3

You'll solve this problem and other issues in the future if you use a DATE column type rather than varchar. This would let you use the date functions in MySQL.

If for some reason you can't or wont you'll need to convert the column to a date before doing the BETWEEN:

SELECT * FROM records 
WHERE STR_TO_DATE(reservations,'%d.%m.%Y') 
BETWEEN '$start_date' AND '$end_date';--Don't use variables directly

Additionally you don't want to use raw inputs in your database query. A malicious user could send SQL to your database and cause damage.

It's better to use escaping, or even better to user parameterized queries. There are questions on this site that can help with that.

edited Aug 3, 2018 at 9:40

answered Aug 3, 2018 at 7:42

Jim

22.7k6 gold badges55 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nikola Kirincic Over a year ago

This is good solution for this case, although, it should be avoided to store date as string in db if possible at first place.

hakkidilek Over a year ago

@Jim When change to code I give this error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STR_TO_DATE('01,5,2013','%d.%m.%Y') BETWEEN '' AND ''' at line 2

Jim Over a year ago

I've switched this to use the reservations field but this is working for me.

ThangLe · Accepted Answer · 2018-08-03 07:51:07Z

0

Your SQL query is using table "reservations" but in images, you are using "rezervasyon_tarihi". Maybe it should be "SELECT * FROM records WHERE rezervasyon_tarihi BETWEEN '$start_date' AND '$end_date' ";

And so, I think this topic will help you SQL Between clause with strings columns

answered Aug 3, 2018 at 7:51

ThangLe

1,0001 gold badge8 silver badges17 bronze badges

1 Comment

hakkidilek Over a year ago

Ok thank you for your suggestion. I know There are similar situation column. But now I dont work on pictures column. Thanks you again.

Collectives™ on Stack Overflow

Using BETWEEN for dates stored as string in MySQL

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related