I need some help with SQL Query.
I am trying to select all records from table test_table
which would not fit between two dates '2009-12-15' and '2010-01-02'.
This is my table structure:
`start_date` date NOT NULL default '0000-00-00',
`end_date` date NOT NULL default '0000-00-00'
-----------------------------
**The following record should not be selected:**
`start_date`, `end_date`
'2003-06-04', '2010-01-01'
My query:
SELECT *
FROM `test_table`
WHERE
CAST('2009-12-15' AS DATE) NOT BETWEEN start_date and end_date
AND
CAST('2010-01-02' AS DATE) NOT BETWEEN start_date and end_date
Any idea why my query select wrong records? Should I change the order of values in query to something like:
start_date NOT BETWEEN CAST('2009-12-15' AS DATE) and CAST('2010-01-02' AS DATE)
Thanks a lot for any help
SQL NOT BETWEEN Operator for Numeric ValueThe SQL NOT BETWEEN operator is used for getting the values as part of result set which is outside of the range specified by the BETWEEN operator. Scenario: Get the percentage of students whose age is not between 11 and 13.
Here is the answer – Technically there is no difference between != and <>. Both of them work the same way and there is absolutely no difference in terms of performance or result.
SELECT * FROM ATM WHERE TRANSACTION_TIME BETWEEN '2005-02-28 21:00:00' AND '2008-12-25 00:00:00';
The SQL BETWEEN Operator The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.
How about trying:
select * from 'test_table'
where end_date < CAST('2009-12-15' AS DATE)
or start_date > CAST('2010-01-02' AS DATE)
which will return all date ranges which do not overlap your date range at all.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With