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

    Dates query in oracle


    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 oracle query 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

  • #767769
    I think you need to take help of Code behind for that, get start date and end date from database and check if next record start date should be starts with last record end date, you can easily write code for it
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #767782

    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.

  • #767796

    As per my understanding you want the records not between the dates period which you given that is your requirement then you can do onething

    First get the records between the period and store it into one temporary table

    select * into #temp from tablename where datefield between @fromdate and @todate

    After get the records into temp table then join it and get the not matched records

    select col1,col2
    from tablename tn,
    #temp t
    where tn.Id !=t.Id

    Hope this helps you...

    Give respect to your work, Instead of trying to impress your boss.

    Blog :

Sign In to post your comments