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

    Gap in dates using c#


    I have a table which contains start date and end date and Date of Joining.
    table records are:

    DOJ -- StartDate -- EndDate
    2016/01/01 2016/01/01 2016/01/31 --- for January month
    2016/01/01 2016/03/01 2016/03/31- -- for march month

    I need c# code to get misssing dates i.e misssing record
    output as :

    DOJ -- StartDate -- EndDate
    2016/01/01 2016/02/01 2016/02/28 -- i.e february month

  • #767781

    YOU CAN try this Query

    ID int primary key identity(1,1),
    EName varchar(50),
    FromDate Datetime,
    ToDate Datetime

    INSERT INTO TESTDATE VALUES('AA','2016/01/01','2016/01/31')
    INSERT INTO TESTDATE VALUES('AA1','2016/01/01','2016/03/31')

    SELECT * FROM TESTDATE WHERE FromDate BETWEEN '2016/01/01' AND '2016/03/31'

    Name : Dotnet Developer-2015
    Email Id :

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #767784
    Hai Ayesha,
    To get the missing month data, you need to CTE(Common Table Expressions) technique something like below:

    declare @startdate DATE = '2016-01-01'
    declare @enddate DATE = '2016-09-30'
    ;with MissingDates (date)
    select @startdate
    Union All
    select dateadd(month, 1, date)
    from MissingDates
    where dateadd(month, 1, date)<=@enddate
    select year(date) as Year, month(date) as Month
    from missingDates

    Hope it will be helpful to you.

    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)

  • Sign In to post your comments