Tech Date range SQL querie not working?

[Publisher Bucks]

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

"SELECT * FROM CookingShows WHERE Date BETWEEN '2022-01-09' AND Date '2022-01-10' ORDER BY Date DESC;");

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

Any thoughts?

[sarettah]

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


"SELECT * FROM CookingShows WHERE Date BETWEEN '2022-01-09' AND Date '2022-01-10' ORDER BY Date DESC;");

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.


.

[Publisher Bucks]

[b]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)"]/b]

.

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.

[redwhiteandblue]

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.

[Klen]

Sample of code which work for me:

SELECT SUM(`P`) AS 'M' FROM `C` WHERE `DateTime` between '$yy-$mm-$dd 00:00:00' AND '$yy-$mm-$dd 23:59:59' 

[blackmonsters]

Edit : already posted above by Sarettah

[Publisher Bucks]

So this is what I have now:

$result = mysqli_query($con,"SELECT * FROM CookingShows WHERE ShowDate (date as DATE) BETWEEN CAST('2022-09-01' AS DATE) AND CAST('2022-10-01' AS DATE)");

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?

[Publisher Bucks]

Sample of code which work for me:

SELECT SUM(`P`) AS 'M' FROM `C` WHERE `DateTime` between '$yy-$mm-$dd 00:00:00' AND '$yy-$mm-$dd 23:59:59' 

Thanks will give that a try now

[sarettah]

WHERE ShowDate (date as DATE)

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....

.

[Publisher Bucks]

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

[k0nr4d]

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.

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.

[redwhiteandblue]

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.

[Nitzer Ebb]

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?

remove date after AND

[Publisher Bucks]

Thanks everyone, I have this working now

[k0nr4d]

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.

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.