SQL & PL/SQL :: Find From And To Date Between Two Fields
Aug 2, 2010
I have oracle table for Leave
LeaveNo - FromDt - ToDt - Query Retrieve
1 - 01/01/2010 - 31/03/2010 - No
2 - 01/02/2010 - 31/05/2010 - Yes
3 - 01/04/2010 - 30/04/2010 - Yes
4 - 25/04/2010 - 15/05/2010 - Yes
5 - 01/05/2010 - 31/05/2010 - No
6 - 15/03/2010 - 14/04/2010 - Yes
7 - 10/04/2010 - 15/04/2010 - Yes
I want find out who was leave on 01/04/2010 to 30/04/2010. user input parameter may vary like 10/04/2010 to 15/04/2010 or 12/04/2010 to 12/04/2010.
how to write oracle sql?
View 12 Replies
ADVERTISEMENT
Aug 16, 2012
I would like to inquire how do I code my select statement if my user enter a search date range(search startDt: 01/08/2012 and search endDt :30/09/2012) and I will like to retrieve only the 7records out from my table as shown below ?
STARTDT_ ENDDT_
01/08/2012 01/08/2012
01/06/2012 31/12/2012
01/08/2012 01/08/2012
01/09/2012 01/09/2012
01/09/2012 31/12/2012
01/07/2012 01/07/2012->not retrieved out
01/08/2012 01/08/2012
01/01/2012 31/12/2012
View 6 Replies
View Related
Aug 15, 2011
I'm having trouble comparing and subtract two date type fields, can not get a significant result.
e.g.
select id, date.started, date.closed, datediff (day, date.started, date.closed) differences diference from Table1 order by desc;
View 4 Replies
View Related
May 19, 2011
TABLE - 1
CIDDOB(DATE)
12312-MAR-63
58918-JAN-78
658927-DEC-43
46515-FEB-80
TABLE - 2
DIDDOB_INFO(VARCHAR2)
34425 Mar 1967
123 12 MAR 1963;25 FEB 1974;25 AUG 1978
46515 FEB 1980
I want to join the columns DOB from table -1 and DOB_INFO from table 2 but the datatype of DOB is DATE and DOB_INFO is VARCHAR. TO_DATE function is not working here.
View 13 Replies
View Related
Jun 25, 2013
I have a requirement of pulling the data from a field in horizontal form of Advanced Supply Chain Planning responsibility(ASCP). When I go into help-> Diagnostics, it shows the block name of the form as 'Horizontal Plan' and field name as 'Pivot Table'. Not pretty sure on how to pull the data source into horizontal form with the info I have.
View 3 Replies
View Related
May 8, 2012
I want to capture date and time in separate fields from user and save it in single column in table
example: user enters date and time
Date : 08/05/20212
Time : 18:50:00
At the time of save I want to save [08/05/2012 18:50:00].
View 1 Replies
View Related
Jul 26, 2010
I want to load a delimited file that contains many records which contained within the table where I'm going to load a date type field and I need to do this by concatenating three fields
field1 = 1 - this is the day
field2 = 11 - this corresponds to the month
field3 = 5 - this corresponds to the year
I need is in the field Save as type date 01/11/2005 i don´t know how to do it but I tried as follows but I get error loading.
"TO_DATE (TO_CHAR (: field1 ||'/'|| DECODE (: field2, 1, 'JAN', 2, 'FEB', 3, 'MAR', 4, 'APR', 5, 'MAY', 6 , 'JUN', 7, 'JUL', 8, 'AUG', 9, 'SEP', 10, 'OCT', 11, 'NOV', 12, 'DEC': field2 )||'/'|| : field3)) "
View 3 Replies
View Related
Jul 31, 2010
SQL> select * from emp;
SINO BOOK UPDATION_
---------- ---------- ---------
1 UB 01-MAR-10
2 UB 12-MAR-10
3 SB 12-MAR-10
4 DB 12-MAR-10
4 MB 12-JUN-10
4 MB 31-JUL-10
6 rows selected.
SQL> SELECT sino, book, updation_date
2 FROM emp
3 WHERE updation_date IN (SELECT MAX (updation_date)
4 FROM emp
5 GROUP BY book);
SINO BOOK UPDATION_
---------- ---------- ---------
2 UB 12-MAR-10
3 SB 12-MAR-10
4 DB 12-MAR-10
4 MB 31-JUL-10
I would like to know, how to find out the latest date from above query without using group functions like max, min,order by and group by.
View 10 Replies
View Related
Nov 5, 2010
building SQL query to get the result as shown below.
Create Table Temp
CREATE TABLE TEMP
(
CASEID NUMBER,
SATUS VARCHAR2(1 BYTE),
TRANS_DATE DATE
)
Insert data
[Code]...
I want to build a query which should give output as shown below. Basically i want to select those rows which having minimum trans_date for a given CASEID & Status.
OUTPUT:
CASEIDSATUSTRANS_DATE
100A11/02/2010
100B11/07/2010
100A11/12/2010
200A11/02/2010
200B11/07/2010
View 6 Replies
View Related
Feb 6, 2013
I am getting daily basis data from third party in excel format which i am converting into CSV format and then uploading into oracle tables using External tables.Now problem is that every time i getting the dates in diff format i.e. sometimes dd-mon-yyyy , dd/mm/yyyy etc.
Now every time i have to open my code and change it there ...to make it as oracle date format. IS there way i can find out format of date and based on format i can do operations with getting errors every time.
I am storing the TP(Excel date) date into varchar columns only and then varchar2 column value i m inserting/updating into date format using to_Date ().
View 9 Replies
View Related
Mar 2, 2013
I have two table in the database. I want to find the maximum routine date for a case_id when the lock_date is between 20 jan to 29 jan 2012 .
But I am not getting actual output (Output routine_date should be "28-jan-2012" but I m getting "31-jan-2012")
Master
case_id lock_date
101 23-jan-2012
101 24-jan-2012
102 27-jan-2012
102 29-jan-2012
101 30-jan-2012
101 29-jan-2012
Routine (for routine work)
case_id routine_date
101 23-jan-2012
103 28-jan-2012
102 21-jan-2012
102 29-jan-2012
101 21-jan-2012
101 28-jan-2012
101 31-jan-2012
select m.case_id, r.routine_date from master m, routine r,
(select case_id, max(routine_date) from routine group by case_id) rr
where m.case_id=r.case_id
and m.case_id=rr.case_id
and r.routine_date=rr.routine_date
View 7 Replies
View Related
Apr 13, 2004
I have two tables
table_1
--Emp_id--|--Sup_id--|Sup_name|--Date--|
-------------------------------------------------
--00001--|--00005 --|---ABCD--|01-MARCH-2004
--00002--|--00006 --|---BCDE--|02-MARCH-2004
--00003--|--00007 --|---CDEF--|03-MARCH-2004
--00001--|--00008 --|---DEFG--|04-APRIL-2004
--00003--|--00009 --|---EFGH--|05-APRIL-2004
table_2
--Emp_id--|Emp_name|
--------------------------------
--00001--|--QWER--|
--00002--|--ASDF--|
--00003--|--ZXCV--|
--00004--|--POIU--|
table_1 contain records on employee and the supervisor they are under at a certain date.
As some employee(00001 & 00003) have a different supervisor from different date, I'll like to extract from table_1 the record of each employee in the table that only contain the supervisor info on the most recent date.And from table_2, i'll like to extract the employee's name. These records extracted from both the tables would the be put into a new table,table_3
Example: For employee 00001, only extract record that have the most recent date which is 04-APRIL-2004 and not on 01-MARCH-2004
table_3
Emp_id|Emp_name|Sup_id|Sup_name|Date|
------------------------------------------------
00001 |--QWER--|00008 |--DEFG---|04-APRIL-2004
00002 |--ASDF-- |00006 |--BCDE---|02-MARCH-2004
00003 |--ZXCV-- |00009 |--EFGH---|05-APRIL-2004
How to write an SQL statement to perform this?
View 6 Replies
View Related
Feb 5, 2011
I am creating report for this i want to make one query..
Query like this
My input is YEAR, MONTH AND WEEK and i want to find out start date and end date for this week...
Scenario like this
-----------------
I/P - Year = 2011, WEEK = 2 , Month - FEB
i will get o/p like this '06-feb-2010' and '12-feb-2010'...
View 10 Replies
View Related
Apr 5, 2011
I require to find the years between any given date range. For example what are the years between the dates '01/12/2010' and '01/02/2012'? Answer must be '2010,2011,2012'. how to code the query for this result?
View 2 Replies
View Related
Dec 18, 2012
we can easily find out the locks which are currently active session.
Is any other way to find out locks on particular date and time using data dictionary?
For example, we have to find whether TT_objects occured the lock or not on ON yesterday.
View 3 Replies
View Related
Jul 2, 2013
My table has two date columns EFF_DT which is the start date and TERM_DT is the end date. The EFF_DT of the next record should be the next date of the TERM_DT record.
My table looks like this.
Input Table:
-----------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1331-Mar-13
Mem1ABC1-Apr-1331-May-13
Mem1ABC1-Jun-1330-Sep-13
Mem1ABC15-Oct-1331-Dec-13
Mem1ABC1-Jan-1431-Mar-14
Mem1XYZ1-Apr-1430-Jun-14
Mem1XYZ1-Jul-1431-Dec-14
Expected Output:
----------------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1330-Sep-13
Mem1ABC15-Oct-1331-Mar-14
Mem1XYZ1-Apr-1431-Dec-14
In the fourth record, the effective date should be 1-Oct-13 which is the next date to the last TERM_DT 30-Sep-13.As the is the break in the date, the output should show 15-Oct-13 sa the second start date.
Note: Refer to the PI_ID columns, there is a break in the date for the sale PI_ID 'ABC'.
Here I am trying to generate a pseudo column, so that the table with the pseudo column looks like as shown below. and I can use first_value and LAST_value by partitioning on the pseudo column to get the desired output.
1) CNT_VAL is the pseudo column:
-----------------------------
CK_IDPI_IDEFF_DT TERM_DT CNT_VAL
Mem1ABC1-Jan-1331-Mar-131
Mem1ABC1-Apr-1331-May-131
Mem1ABC1-Jun-1330-Sep-131
[code].....
My Query :
----------
I not getting the desired output here as the value in pseudo column is 3.
select CK_ID, PI_ID,EFF_DT,TERM_DT,
(case
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) = 0 then to_char(case_CONT)
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) <> 0 then to_char(LAG(case_CONT,1) over (ORDER BY EFF_DT) + 1)
else to_char(nvl(case_CONT,0))
[code].....
Scripts:
--------------------
Create table lead_test(
CK_ID varchar2(10),
PI_IDvarchar2(10),
EFF_DTDate,
TERM_DT date);
[code].....
View 1 Replies
View Related
Sep 14, 2012
The requirement is to find the last date when either ATTRIB1 or ATTRIB2 were modified for each ID. There are many other columns in the table, but I'm not interested in other columns.
Test Case
SET LINES 100
SET PAGES 100
DROP TABLE test_log
/
CREATE TABLE test_log
[code]....
Expected Output
ID LAST_CHANGE_DATE
---------- --------------------
11 02-SEP-2012 10:58:32
35 05-AUG-2012 10:58:32
I have written the below query, to get the required output.
SELECT MAX(i_date) last_change_date
FROM
(
SELECT seq,id, attrib1, attrib2, i_date,
row_number() OVER (PARTITION BY attrib1 ORDER BY i_date) rn1,
row_number() OVER (PARTITION BY attrib2 ORDER BY i_date) rn2
[code]....
View 6 Replies
View Related
Sep 3, 2010
Lets say I have a table,
Claim_id,dateA,dateB
it has 5 million rows
I need to see if any dateA,dateB of a claim_id falls within any other dateA,dateB of another claim ID
Basically
select * from table a, table b, where
(a.dateA,a.dateB) overlaps (b.dateA,b.dateB)
now I can write the query simply enough by aliasing the table 2x but no matter how I try I cant see a way to get around doing a Carteasion join
Index are ignored because it has to scan the full table anyway even if I hint the index
and the cost of the join ends up astronomical aka 5million rows X 5 million Rows....
And it ends up doing the full table scan a few times
AHHHHHHHHHHHHHHHHHH
this table is expected to grow to at least 20 million records
View 17 Replies
View Related
Jul 8, 2010
i want to find out difference between two dates in day-month-year. How can i do it?
For example how many days,months are years are between '01-jul-1979' and '08-jul-2010'
View 4 Replies
View Related
Jun 20, 2013
How to find date and time of a column in table ?
say for example there is a column called 'date_txn' in a table .When i select that particular column it display output as 'June 2013'.But i want output "with date and time".
View 10 Replies
View Related
Nov 7, 2013
I have a table which contains some data. But we are dropped and recreated the same table several times. Now I wanted to know that when this table created initially. How to findout the date of creation(very first time).
View 13 Replies
View Related
Jun 3, 2010
I have to create a function. I need to find the max last logout date for each agent daily. For example, if an agent logged in for the first time at 9:00 and he logged out at 12:00 and he logged in again in 14:00 and he logged out at 15:00 the time I need my report to show is 15:00. How can I do that?In order to make it easiest for you to understand I am sending you this query:
select
a.login as login2,
To_Char(max(s.endtime), 'dd/MM/yyyy, HH24:MI:SS') as lastLogout
from cti.agent a
inner join cti.agentsessionlog s
on s.agentid = a.agentid and To_Char(s.endtime) != '31-DEC-99 11.59.59.000000 PM'
group by a.login;
This query returns the agent's login and the agent's last logout time. It works fine if I enter a date between but I cannot do that. If a use this query as it is and I try to export a report for 31/5 it shows as lastlogout the logout for 01/06 or 2/06. Is there a function I can use? I have a deadline.
View 7 Replies
View Related
Mar 21, 2013
I want to find the row with invalid day, month which are not matching with calendar day and month. Also the program should capture the data if the year <1900
with xx as
(select 101 as ID, '24/05/1899' as create_date from dual
union all
select 101, '32/03/2012' from dual
union all
select 102 ,'30/02/2012' from dual
union all
select 101 , '29/02/2013' from dual
[code]...
View 16 Replies
View Related
Feb 3, 2011
I given the table name,column name,datatype and sample record in the table. I have given the sample record for 01-jan-2008 to 8-Jan-2008, but in the real thing it will be for 30 years.
My Requirement:
For each class_no (202,203..), I need the missing date excluding weekends (sat, sun), I have provided the sample output below.
Table Name : ABC
Column Name : Class_no Data Type : Number
Column Name : Class_DateData Type : Date
Sample Record in the Table :
Class_noClass_Date
202 1-Jan-08
202 2-Jan-08
202 7-Jan-08
202 8-Jan-08
203 1-Jan-08
203 2-Jan-08
203 3-Jan-08
203 7-Jan-08
203 8-Jan-08
OUTPUT:
Class_noClass_Date
202 3-Jan-08
202 4-Jan-08
203 4-Jan-08
View 5 Replies
View Related
Mar 10, 2011
We are trying to audit a view. We are currently seeing that view in production instance and trying to find out when exactly it got invalidated.
View 4 Replies
View Related
Apr 27, 2013
create table call(id number(10) primary key,mobile_no number(10), other_no number(10), call_type varchar2(10),call_date_time date, duration number(10));
insert into call values(1,9818764535,9899875643,'IN','24-APR-13 02:10:43',10);
insert into call values(1,9818764535,9898324234,'IN','24-APR-13 05:06:78',10);
insert into call values(1,9818764535,9215468734,'IN','24-APR-13 15:06:78',10);
insert into call values(1,9818764535,9899875643,'OUT','25-APR-13 01:06:78',10);
insert into call values(1,9899875643,9899875643,,'OUT','25-APR-13 22:06:78',10);
Query : need to find first and last call of '9818764535' mobile number and of call_date between '24-apr-13' and '25-apr-13';
Result :
date ,mobile_no , other_no, call_type, duration
24/apr/13 , 9818764535,9899875643,'IN',10
24/apr/13 ,9818764535,9215468734,'IN',10
[Code]....
View 5 Replies
View Related
Jun 1, 2010
I'm trying to work out how to take a table like this:
IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09
And display the data like this:
IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09
As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.
View 13 Replies
View Related
Apr 12, 2010
I have a two date fields in my form; valid from date and expiry date.
Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.
I need my expiry date to automatically show a date 15 years after this date?
View 8 Replies
View Related
Jul 21, 2011
Once a year in the application we have a specific query that gets used a lot. It's an UPDATE that updates a single record in a single table with a few different datatypes, but the issue is happening with one of the VARCHAR2 fields. It updates one VARCHAR2(2000) and three VARCHAR2(4000) fields at the same time.
This year, 9 of the 95 times it was used resulted in one of the VARCHAR2(4000) fields as null in the database. The users would not want this field to be null and 5 of the 9 have told us they entered something (the form they're filling out is a research proposal and leaving this field empty would be pointless because it's part of the funding request, so they're not doing it). The application isn't doing it because it's not consistent. I've checked the application and these fields can't be nulled any other way.
We just found the issue so I looked back over the past years back to 2005. Last year it didn't happen at all. In 2010 it happened a handful of times. Some years there were even more times. It's not always the same field but it's always a VARCHAR2 of at least 2000 characters.
I have a lot more information but it's all just details (let me know if you need to know more). I'm wondering if there is a bug in 10g with these types of fields. I don't believe it's malicious behavior on an individual's part but I suppose that's always possible.
how to research something like this. I tried to get access to Oracle Support and the Knowledge Base I heard they have but it doesn't look like I can do that
View 2 Replies
View Related
Mar 15, 2007
I have the following fields:
Addressln1
Addressln2
Suburb
Town
I know how to concatenate them
trim(Addressln1) || ',' || trim(Addressln2) || ',' || trim(Suburb) || ',' || trim(Town) as Address
1. I would like to know if any of the fields are empty I would like to eliminate the comma character from the string.
2. Can I replace the comma with a new line character and what character to be used in the syntax.
View 2 Replies
View Related
