no of Dates Between two dates

Question

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

Midhun T P · 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 T P · Answer

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 patil · Answer

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 Rathor · Answer

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.