CODEWITHSUNDEEP
sql
Stagleton
Stagleton

Reputation: 1060

SQL query error or is it somewhere else?

I am trying to query an sql database and I am having trouble. Is there an error with the syntax below or is it something else that I am missing?

SELECT Name, DateTime, num1, num2 
FROM names_E6 
WHERE DateTime between '2012-1-27' AND '2012-1-27' 
WHERE num1 between '0' AND '5' 
WHERE num2 between '0' AND '6'

Upvotes: 1

Views: 68

Answers (2)

user330315
user330315

Reputation:

In addition to the answers regarding the syntax, I would like to add a comment on the usage of value literals:

If num1 and num2 are numbers, the condition should be

AND num1 between 0 AND 5  
AND num2 between 0 AND 6

Note the missing single quotes around the numbers. Some DBMS will not even allow a conversion from a character literl '5' to a number, others might surprise you with strange results and most of them will not use an index on the column if such an implicit type casting is successful.

You should also be aware that the expression DateTime between '2012-1-27' AND '2012-1-27' might not always work correctly due to the implicit type casting (String -> Date) and locale settings of the server and/or client. To be on the safe side you should use e.g. the ANSI standard for a date literal:

 DateTime between DATE '2012-01-27' AND DATE '2012-01-27'

Note the keyword DATE to start a date literal and the month given with two digits.

Upvotes: 2

user596075
user596075

Reputation:

Yes, you can't have multiple WHERE clauses in one SELECT. Separate the conditions with an AND or an OR (by the looks of your query, I'm guessing you want AND).

SELECT Name, DateTime, num1, num2  
FROM names_E6  
WHERE DateTime between '2012-1-27' AND '2012-1-27'  
AND num1 between '0' AND '5'  
AND num2 between '0' AND '6'

Upvotes: 5

Related Questions