How to find Missing dates of two dates in asp.net C# code

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	5

hi Developers,

i want to get all Missing dates of two dates in asp.net C# code dynamically and i need to bind the dates. i have last Inserted record and Current date but i dont know how to bind the missing dates one by one.

this is my database Records
E001 07/08/2017
E001 08/08/2017
E001 11/08/2017

in this record 09/08/2017,10/08/2017 dates are missing i want to bind the two dates one by one.
if suppose my record is like
E001 01/08/2017
E001 11/08/2017

i want to bind the 10 missing dates of database.
friends if anyone of you know please suggest me to how i am done this task.

thanks with
Paul.S
"A man becomes what he thinks about"

Joined:	13/01/2015
Level:	Silver
Points:	230

Points:	4

Hey Paul,

You can find min and max of your dates and query to get the dates between them . you can use either of the below two methods:

1.) Using CTE

DECLARE @MinDate varchar(10) = '08/08/2017' -- dateForat : MM/DD/YYYY
,@MaxDate varchar(10) = '08/20/2017'

;WITH GetDates
AS (
SELECT DATEADD(day, 1, cast(@MinDate as date)) AS TheDate

UNION ALL

SELECT DATEADD(day, 1, TheDate)
FROM GetDates
WHERE TheDate < cast(@MaxDate as date)
)
select * from GetDates

2.) Query Inbuilt Table 'sys.all_objects'

DECLARE @MinDate varchar(10) = '08/08/2017'
,@MaxDate varchar(10) = '08/27/2017'

SELECT TOP (DATEDIFF(DAY, cast(@MinDate as date), cast(@MaxDate as date)) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, cast(@MinDate as date))
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;

~Cheers!!

Thanks!
Anjali Bansal

~Give your best and lead the world

How to find Missing dates of two dates in asp.net C# code

About dotnetspider.com

Quick Links

General

Services