?
Solved

"between" sql query

Posted on 2004-09-27
6
Medium Priority
?
18,572 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 49

Assisted Solution

by:Roonaan
Roonaan earned 80 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 80 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 80 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 80 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 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