Solved

"between" sql query

Posted on 2004-09-27
6
18,570 Views
Last Modified: 2011-08-18
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
Comment
Question by:TPoly
6 Comments
 
LVL 49

Assisted Solution

by:Roonaan
Roonaan earned 20 total points
ID: 12158506
possibly: BETWEEN "2003-11-01" AND "2004-01-31"

-r-
0
 
LVL 1

Expert Comment

by:frank_van_puffelen
ID: 12158577
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
 
LVL 1

Assisted Solution

by:frank_van_puffelen
frank_van_puffelen earned 20 total points
ID: 12158605
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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 20 total points
ID: 12160377
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
 
LVL 11

Assisted Solution

by:sciber_dude
sciber_dude earned 20 total points
ID: 12160515
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
 
LVL 11

Expert Comment

by:sciber_dude
ID: 12160534
oops.. frank van puffelen has already suggested the same. Disregard my post.

:) SD
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question