You must Sign In to post a response.
  • Category: ASP.NET

    How to find Missing dates of two dates in C# code

    hi Developers,

    i want to get all Missing dates of two dates in 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
    "A man becomes what he thinks about"
  • #769328
    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


    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;


    Anjali Bansal

    ~Give your best and lead the world

  • Sign In to post your comments