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

    Dates query in oracle

    Hi,

    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


    Thanks.
  • #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
    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #767782
    Hi

    try this Query



    CREATE TABLE TESTDATE
    (
    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 :kumaraspcode2009@gmail.com

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

  • #767796
    Hi,

    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.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments