Sql Query Not Between Two Dates

SQL Query NOT Between Two Dates

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.

SQL Select rows which are not between dates or not in the table

You need a left join to try and find rooms without an applicable reservation record.

SELECT 
ROOM_DESCRIPTION_DESC AS "DESCRIPTION",
COUNT(ROOM_DESCRIPTION_ID) AS "AMOUNT"
FROM ROOM_TAB
JOIN ROOM_DESCRIPTION_TAB ON ROOM_DESCRIPTION = ROOM_DESCRIPTION_ID
LEFT JOIN RESERVATION_TAB ON RESERVATION_ROOM = ROOM_ID
    AND RESERVATION_FROM NOT BETWEEN '2014-02-10' AND '2014-02-11'
WHERE RESERVATION_ID IS NULL
GROUP BY  ROOM_DESCRIPTION_DESC

Datetime BETWEEN statement not working in SQL Server

Do you have times associated with your dates? BETWEEN is inclusive, but when you convert 2013-10-18 to a date it becomes 2013-10-18 00:00:000.00. Anything that is logged after the first second of the 18th will not shown using BETWEEN, unless you include a time value.

Try:

SELECT 
* 
FROM LOGS 
WHERE CHECK_IN BETWEEN 
    CONVERT(datetime,'2013-10-17') 
    AND CONVERT(datetime,'2013-10-18 23:59:59:998')

if you want to search the entire day of the 18th. I set miliseconds to 998 because SQL Server was pulling in 2013-10-19 00:00:00:0000 in the query.

SQL DATETIME fields have milliseconds. So I added 999 to the field.

SQL NOT BETWEEN query

This should work but should look something more like

select table2.testfield
FROM table2, table1
WHERE table1.YourField = '2011-02-24 18:00:00' 
AND
NOT BETWEEN table1.start AND table1.finish

This also presumes that your table1.start and table1.finish fields are of type DateTime. If they aren't you could try Casting the fields

select table2.testfield
    FROM table2, table1
    WHERE table1.YourField = '2011-02-24 18:00:00' 
    AND
    NOT BETWEEN Cast(table1.start as DateTime) AND Cast(table1.finish As DateTime)

Edit Looking at your question I realized that the date probably isn't a database value :) so your method should work but you may need to cast the string to a datetime.

SQL Query to check if a date doesn't exist between two dates

My first suggestion would be to use your calendar table, if you don't have one, then create one. They are very useful. Your query is then as simple as:

-- DUMMY DATA
WITH Schedule (Day) AS (SELECT CONVERT(DATE, D) FROM (VALUES ('20190101'), ('20190102'), ('20190104')) x (D))

SELECT  Date
FROM    dbo.Calendar AS c
WHERE   c.Date >= '20190101'        -- ADD A START DATE THAT SUITS YOUR NEEDS
AND     c.Date < '20190105'         -- ADD AN END DATE THAT SUITS YOUR NEEDS
AND     NOT EXISTS
        (   SELECT  1
            FROM    Schedule AS s
            WHERE   s.Day = c.Date
        );

If you don't want to, or can't create a calendar table you can still do this on the fly pretty easily:

DECLARE @StartDate DATE = '2019-01-01',
        @EndDate DATE = GETDATE();

WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Dates AS
(   SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
    FROM N3
)
SELECT  *
FROM    Dates;

This will simply produce a list of all dates within the given date range. For further reading on this see:

• Generate a set or sequence without loops – part 1
• Generate a set or sequence without loops – part 2
• Generate a set or sequence without loops – part 3

You would then just need to exclude rows where a schedule exists for that date:

DECLARE @StartDate DATE = '20190101',
        @EndDate DATE = '20190105';

WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Dates AS
(   SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
    FROM N3
),
-- DUMMY DATA
Schedule (Day) AS (SELECT CONVERT(DATE, D) FROM (VALUES ('20190101'), ('20190102'), ('20190104')) x (D))

SELECT  *
FROM    Dates AS d
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    Schedule AS s
            WHERE   s.Day = d.Date
        );

OUPUT

Date
----------
2019-01-03
2019-01-05

SQL query to select dates between two dates and exclude dates between two dates

If you want to select rows having dates between two date values and which are not in an other table, first select the rows using Between .. and in a Where clause and omit the dates which are part of other table using Not Exists.

Query

select * from [dbo].[#temp1] as [t1]
where cast([workDate] as date) between '2018-09-01' and '2018-12-01'
and not exists(
    select 1 from [dbo].[#temp2] as [t2]
    where [t1].[workDate] = [t2].[workDate]
);

Problems with BETWEEN dates operator

If you're using the W3Schools Tryit editor in Chrome, you're using WebSQL, which is basically SQLite.

SQLite doesn't have a date/time format, so is probably storing the date values as strings formatted in the ISO-8601 format (see this answer for more information).

Other database systems (e.g. Oracle, Microsoft SQL Server, Postgres, MySQL) have built-in date formats, and you generally represent them as strings (enclosed in single quotes). For example: '1997-07-01' (depending on the specific RDBMS, there might be more specific considerations).

The format that uses pound signs (e.g. #7/1/1997#) is unique to Microsoft Access (see this answer for more information).

---

Bottom line: Dates are generally enclosed in single quotes. You're best off sticking to the ISO-8601 standard (e.g. 1997-07-01).

If you're learning SQL, there are other resources out there besides W3Schools. I would recommend downloading an open-source RDBMS like Postgres or MySQL, setting up a sample database, and working on some queries. Challenge sites like codewars might also be helpful

---

One more thing: Don't use BETWEEN for dates. Use >= and <, to make sure you're not excluding dates with a time portion. For more information, read this blog.

---

Related Topics