Category: .NET

03 Sep 2016 22:21

Joined:	30/08/2016
Level:	Bronze
Points:	14

Points:	5

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

06 Sep 2016 02:46

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	2

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

07 Sep 2016 22:09

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	3

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

09 Sep 2016 05:22

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	4

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/

Dates query in oracle

About dotnetspider.com

Quick Links

General

Services