Select Count days between two dates using LINQ

Question

I need to get the number of holidays between two dates. I have tried using the query below but I'm getting an incorrect count number.

StartDate	EndDate
01/2/2022	03/2/2022
17/2/2022	19/2/2022

SELECT COUNT(*) FROM table
WHERE StartDate <= '02/2/2022' and EndDate >= '19/2/2022'

how I make this date => '02/2/2022' return count of day that between two dates from the first row from the table and get count day from the second row.

The count must be 5 days.

Isn't it easier to search before asking a redundant question. Q: tsql get hollydays between 2 dates => stackoverflow.com/questions/12803823/… — spzvtbg
– spzvtbg, Commented Apr 14, 2022 at 14:21
@AndrewTruckle No, it's not clear to me, if you see the table and query you find select must be started from '2/2/2022', and this date found between '1/2/2022' and '3/2/2022', — Mahmoud Omar
– Mahmoud Omar, Commented Apr 14, 2022 at 15:50
I often try to get the query to work as desired in Access Query Interface. Ince it works there I transfer into my software however appropriate. — Andrew Truckle
– Andrew Truckle, Commented Apr 14, 2022 at 18:05

Caius Jard · Accepted Answer · 2022-04-15 06:04:29Z

0

The simplest way of doing it is to have a table that has one row per day for every day from eg year 2000 to year 2099, then you can:

SELECT COUNT(*) 
FROM
  cal           --your dates table
  INNER JOIN h. --your holiday table 
  ON cal.d BETWEEEN h.start AND h.end
WHERE
  cal.d BETWEEN '2022-02-02' AND '2022-02-19'

The join will produce 6 rows, the where trims it to 5, there is your count

Generating the calendar table, or its equivalent (an arbitrary series of dates) is an exercise for the reader.. See something like this

answered Apr 15, 2022 at 6:04

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select Count days between two dates using LINQ

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related