CODEWITHSUNDEEP
phpmysqlsql
y2ok
y2ok

Reputation: 658

SQL Query is not working as it should

So basically I have a table -

ID | from | To

-----------------
1  | 25.05.2012|30.05.2012
-----------------
2  | 15.05.2012|20.05.2012
-----------------
3  | 25.06.2012|30.06.2012

and I have a query

SELECT date.*
FROM table AS date
WHERE (date.from >= '25.05.2012' OR date.to >= '25.06.2012' )  AND (date.to <= '30.05.2012' OR date.from <= '25.05.2012' )
GROUP by date.id

but it's not working, what could be the problem?

Upvotes: 0

Views: 80

Answers (5)

fire
fire

Reputation: 21531

date is a reserved keyword in MySQL, try calling the table something else!

Upvotes: 1

Fahim Parkar
Fahim Parkar

Reputation: 31647

Point 1 : table, date, from are reserved keywords. Those should not be used for naming tables. If your table name is table, use backticks

Point 2 : When you have one table why you are creating alias for that?

Point 3 : While comparing date should be in format of yyyy-mm-dd. I believe from and to columns are timestamp.

Point 4 : No need of GROUP BY statement at the moment as you are not using any aggregate function such as SUM, COUNT, etc

Your query should be 

SELECT *
FROM `table`
WHERE 
    (`from`>= '2012-05-25' OR `to`>= '2012-06-25' ) 
    AND
    (`to` <= '2012-05-30' OR `from` <= '2012-05-25');

If you want to pass date as 25.05.2012 , use STR_TO_DATE function to convert string to date.

Upvotes: 0

Martin M&#252;ller
Martin M&#252;ller

Reputation: 2535

"date" is a reserved string in MySQL. I'd recommend renaming all tables and columns with that name to a custom one. This might already solve your problem.

Upvotes: 0

Farahmand
Farahmand

Reputation: 2991

SELECT * FROM table WHERE (from >= '25.05.2012' AND to <= '25.05.2012') GROUP by id;

Upvotes: 0

Rich O&#39;Kelly
Rich O&#39;Kelly

Reputation: 41767

You are performing string comparisons, not date comparisons, eg: 26.05 is greater than 25.06 since the comparison only reaches as far as the second character of the string and determines that 6 is greater than 5.

In order to perform date comparisons you either need to store the value as a date (preferable) or in an appropriate string form eg yyyyMMdd.

Upvotes: 1

Related Questions