[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

"between" sql query

Posted on 2004-09-27
6
Medium Priority
?
18,574 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

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…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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

656 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