Tech Date range SQL querie not working? - GoFuckYourself.com

Publisher Bucks · 08-14-2022, 08:23 AM

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

Quote:

"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 · 08-14-2022, 08:48 AM

Quote:

Originally Posted by 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?

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 · 08-14-2022, 09:09 AM

Quote:

Originally Posted by sarettah

[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 · 08-14-2022, 09:54 AM

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 · 08-14-2022, 10:04 AM

Sample of code which work for me:

PHP Code:


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

blackmonsters · 08-14-2022, 10:16 AM

Edit : already posted above by Sarettah

Publisher Bucks · 08-14-2022, 11:10 AM

So this is what I have now:

Quote:

$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 · 08-14-2022, 11:10 AM

Quote:

Originally Posted by Klen

Sample of code which work for me:

PHP Code:


			
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 · 08-14-2022, 02:30 PM

Quote:

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 · 08-14-2022, 05:04 PM

Quote:

Originally Posted by sarettah

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 · 08-15-2022, 12:42 AM

Quote:

Originally Posted by 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.

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 · 08-15-2022, 06:07 AM

Quote:

Originally Posted by k0nr4d

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 · 08-15-2022, 02:47 PM

Quote:

Originally Posted by Publisher Bucks

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 · 08-15-2022, 02:51 PM

Thanks everyone, I have this working now

k0nr4d · 08-16-2022, 12:44 AM

Quote:

Originally Posted by redwhiteandblue

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.

08-14-2022, 09:54 AM	#4
redwhiteandblue Bollocks Industry Role: Join Date: Jun 2007 Location: Bollocks Posts: 2,792	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. __________________ Interserver unmanaged AMD Ryzen servers from $73.00

08-14-2022, 10:04 AM	#5
Klen Industry Role: Join Date: Aug 2006 Location: Little Vienna Posts: 32,235	Sample of code which work for me: PHP Code: SELECT SUM(`P`) AS 'M' FROM `C` WHERE `DateTime` between '$yy-$mm-$dd 00:00:00' AND '$yy-$mm-$dd 23:59:59'

08-14-2022, 10:16 AM	#6
blackmonsters Making PHP work Industry Role: Join Date: Nov 2002 Location: 🌎🌅🌈🌇 Posts: 20,268	Edit : already posted above by Sarettah __________________ Make Money with Porn

08-15-2022, 02:51 PM	#14
Publisher Bucks Confirmed User Industry Role: Join Date: Oct 2018 Location: New Orleans, Louisiana. / Newcastle, England. Posts: 1,126	Thanks everyone, I have this working now __________________ SOMETHING EXTREME IS COMING SOON!