[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 18575
  • Last Modified:

"between" sql query

mysql> select qin, qout, yield, reject FROM yield WHERE date BETWEEN '11/01/2003' AND '01/31/2004';

hi...
i have this sql statement that does not extract the query i want
It did not even extract anything

this is how the table look like
+-----------+------------+------+------+------+-------+--------+--------+---------------------+
| job       | date       | lot  | qin  | qout | yield | reject | report | dir                 |
+-----------+------------+------+------+------+-------+--------+--------+---------------------+
| HDMP-0440 | 01/31/2004 | 321  |   15 |   15 |   100 |      0 | yield  | /home/student       |
| HDMP-0440 | 11/31/2003 | 456  |   15 |   15 |   100 |      0 | yield  | /home/student/htm   |

so how do i change this sql query?
0
TPoly
Asked:
TPoly
4 Solutions
 
RoonaanCommented:
possibly: BETWEEN "2003-11-01" AND "2004-01-31"

-r-
0
 
frank_van_puffelenCommented:
As Roonaan already suggests this problem is mos likely caused by the date format that you use in your SQL. Even though the format you use in your SQL matches the one in the output of the SELECT it can still cause problems. It is best to use a format that leaves no room for wrong interpretation. The format Roonaan suggests could work, but on the MySQL site they tend to favour a format without any separators in there, like "20040131".
0
 
frank_van_puffelenCommented:
I did some extra checks in the MySQL online documentation and found that they actually normally use separators in their date values, like
'1997-10-04 22:23:00'.

If you really want to play it safe you should probably convert the string to a data using the MySQL STR_TO_DATE function. This allows you to specify your own data format, so it can be anything you want. Something like STR_TO_DATE('03/10/2003', '%m.%d.%Y') should work for the values you specified in your question.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hopefully the table column is defined as date, and not using a char data type...
if it is defined as char, you should consider to change it to date (migrating the data!!!), otherwise the above GOOD comments won't help alot!
CHeers
0
 
sciber_dudeCommented:
It definitely does appear that the date column was defined as char or varchar coz, the default method of storing date in MySQL is YYYY-MM-DD. And from his/her data, it doesnt look it.

If thats the case, as angel suggested, data migration and requerying will be necessary.
My Two cents.
:) SD
0
 
sciber_dudeCommented:
oops.. frank van puffelen has already suggested the same. Disregard my post.

:) SD
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now