Date range SQL querie not working?
 

This is what I'm using which, from what I have seen online, should be working:

Multiple sites are saying that is the correct format, yet it isnt displaying any data that I have in the table.

Any thoughts?

1. You have the syntax wrong. The correct syntax is "where date between date1 and date2" not "where date between date1 and date date2".

2. You are trying to compare a string to a date. You need to cast the strings as dates if you want a date compare. "where date between cast(date1 as date) and cast(date2 as date)"

3. Date is a keyword in MYSQL and should not be used as a variable name or field name. So where you have your column called date ("where Date...") should be something like "where order_date...." Or whatever other name the date is stored as.


.

Thanks.

I renamed the column to showdate and also redid the syntax, which is strange as StackOverFlow had tons of people saying that worked as I posted lol

Can you explain that part above for me a little? I'm not quite sure what you mean.

MySQL (and PHP for that matter) does not magically recognize '2022-01-10' as a date. It's just a string with some numbers and hyphens in it. You can compare two strings but it's not the same as comparing dates. You have to tell it it's a date by casting it from "string" type to "date" type.

Sample of code which work for me:

Edit : already posted above by Sarettah




:2 cents:

So this is what I have now:

It still doesnt show me any data.

The column is set to varchar(255) if that makes any difference? It isnt set as date, datetime, or anything like that, should it be?

Thanks will give that a try now :thumbsup

Where did you get that from?

It should be "where ShowDate between......"

Unless you are storing a string in the database instead of a date. In that case it would be

"where cast(ShowDate as date) between....

.

I threw it together lol

Thank you :thumbsup

Mysql actually does - you don't have to cast it as a date. BETWEEN '2022-01-01' AND '2022-12-31' will work fine. It's possible it might not work if you have strict mode on but overall it does parse it by itself.

It will work but technically it's because the string comparison works with the date in that format. MySQL doesn't know those strings contain dates until you do something date related with them like assigning them to a date column.

remove date after AND

Thanks everyone, I have this working now :)

Well I mean yeah, I suppose you are right in that respect - it works if you use a DATE or TIMESTAMP field but I suppose it might not work without casting if you have it in a VARCHAR or something.