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

CREATE TABLE [dbo].[#temp1]
(
[workDate] [datetime] NULL,
[Id] [int] NULL,
) ON [PRIMARY]

INSERT INTO [dbo].[#temp1]
VALUES ('12-01-2018', '11'), ('11-01-2018', '11'),
('10-01-2018', '11'), ('09-01-2018', '11')


CREATE TABLE [dbo].[#temp2]
(
[workDate] [datetime] NULL,
[Id] [int] NULL,
) ON [PRIMARY]

INSERT INTO [dbo].[#temp2]
VALUES ('10-01-2018', '11'), ('09-01-2018', '11')


I have 2 tables with dates.

I want to select all the dates from #temp1 but do not count the dates from #temp2.

#temp1

#temp2

I used, but did not get the desired result:

select A.workDate, A.Id
from [dbo].[#temp1] A
left join [dbo].[#temp2] B on A.Id = B.Id and A.workDate = B.workDate
where A.workDate between CAST('09.01.2018' as datetime) and CAST('12.01.2018' as datetime)
or B.workDate not between CAST('09.01.2018' as datetime) and CAST('10.01.2018' as datetime)


The result I want to get:

workDate Id
-------------------------------
2018-01-12 00:00:00.000 11
2018-01-11 00:00:00.000 11


How to fix it?

4 Answers
4

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.

Between .. and

Where

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]
);


The way you wrote the query depends on the values in the tables. If you already know the dates you want to select, you can just do this:

where A.workDate between CAST('11.01.2018' as datetime) and CAST('12.01.2018' as datetime)


What you probably want instead is this, to select rows where the left join does not match anything:

left join

where B.workDate is null


Update your query with and condition as below. And check that B.workDate IS NULL so that is record doesn't exist in [dbo].[#temp2] then also it will give the result.

and

B.workDate IS NULL

[dbo].[#temp2]

select A.workDate, A.Id
from [dbo].[#temp1] A
left join [dbo].[#temp2] B on A.Id = B.Id and A.workDate = B.workDate
where A.workDate between CAST('09.01.2018' as datetime) and CAST('12.01.2018' as datetime)
and (B.workDate IS NULL or B.workDate not between CAST('09.01.2018' as datetime) and CAST('10.01.2018' as datetime)


A.Id=B.Id - this condition in join predicate is logically wrong/makes no sense. You were talking about comparing dates - what would Ids be here for? Same thing about OR part in where.

A.Id=B.Id

Id

OR

where

As you stated your job: dates from #temp1 except dates from #temp2 - it can be "translated" from english into SQL almost word to word:

select A.workDate
from [dbo].[#temp1] A
where A.workDate between CAST('09.01.2018' as datetime) and CAST('12.01.2018' as datetime)

EXCEPT

select B.workData
from [dbo].[#temp2] B


or "dates from #temp1 which do not exist in #temp2":

select A.workDate,A.Id
from [dbo].[#temp1] A
where A.workDate between CAST('09.01.2018' as datetime) and CAST('12.01.2018' as datetime)
AND not exists(select 1 from [dbo].[#temp2] B WHERE B.workDate = A.workDate)


Yes, that simple.

Also you better don't cast dates like that: CAST('09.01.2018' as datetime). "Default" date format depends on settings and can be, e.g.

CAST('09.01.2018' as datetime)

ddmmyy

yymmdd

mmddyy

So this cast may result different dates (month 09 day 01 or month 01 day 09?)
Use CONVERT(datetime, '09.01.2018', 104). And better tend not to use literals and magic strings/numbers within a query - put those values into variables and use them like that:

CONVERT(datetime, '09.01.2018', 104)

declare @date_begin date = CONVERT(date, '20180109', 112),
@date_end date = CONVERT(date, '20180112', 112)

select A.workDate,A.Id
from [dbo].[#temp1] A
where A.workDate between @date_begin and @date_end
AND not exists(select 1 from [dbo].[#temp2] B WHERE B.workDate = A.workDate)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.