Loading

no of Dates Between two dates

no of Dates Between two dates

prashant patil

9y901

hi Guys

i want to no of dates Between Two dates

Example:-

Start Date 03 - 11 - 2016

end date 07-11-2016

Result is

04-11-2016

05-11-2016

06-11-2016

Thanks

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Midhun Tp9y ago
Accepted answer
Hi,

Found below query from net -
DECLARE @MinDate DATE = '20161103',
@MaxDate DATE = '20161107';
SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate)-1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id), @MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
Midhun Tp9y ago
Hi,

You can change query as below -
DECLARE @MinDate DATE;
DECLARE @MaxDate DATE;
SELECT @MinDate=CONVERT(VARCHAR(10),fromdate,112), @MaxDate=CONVERT(VARCHAR(10),todate,112) FROM T_LeaveApplication
SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate)-1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id), @MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;

Here fromdate and todate are column names.
prashant patil9y ago
Midhun T P

Thaks It's Working But My Table Name is T_LeaveApplication where i put this table Name
please help.

Thanks For Answer

Brijesh Rathor9y ago

Hi,

you can do like this,

public IEnumerable<DateTime> DateRange(DateTime fromDate, DateTime toDate)

{ return Enumerable.Range(0, toDate.Subtract(fromDate).Days + 1) .Select(d => fromDate.AddDays(d)); }

hope this will help u.