Solved

"between" sql query

Posted on 2004-09-27
6
18,569 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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