SQL & PL/SQL :: Select Date Values From A Table Between Two Dates?
Feb 16, 2010
Oracle 10g
In a table I have a column update_date and its type is DATE. Sample values from this column are as follows. I am using the following query to select all update_date lie between sysdate and sysdate-90.
select update_date from table1
where update_date between sysdate and sysdate-90
The above query retrun no data even data is there in the table for this range.
Update_date
11-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
[code]...
View 7 Replies
ADVERTISEMENT
Mar 10, 2011
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------
Result :
X1 : -----------[--]--------------------
X2 : -----------------------[--------]--
Sample 2:
Travel Date : -[--------------------------------]--
BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------
Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--
How can I do it using PL SQL ?
View 5 Replies
View Related
Aug 31, 2010
I have the below data in table test_1.
select * from test_1
IDNameTotal
-----------
1A100
2B100
3C100
4D100
test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2.
select * from test_2
ID
----
1,2,3
My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.
SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)
create table test_1 (id number, name varchar2(100), total number)
create table test_2(id varchar2(100))
insert into test_1 values (1,'A',100)
insert into test_1 values (2,'B',100)
insert into test_1 values (3,'C',100)
insert into test_1 values (4,'D',100)
View 4 Replies
View Related
Oct 22, 2003
How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:
12/2/2003
12/3/2003
12/4/2003
12/5/2003
Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.
View 14 Replies
View Related
Apr 23, 2010
how to select 1st record from duplicate vales in a table.
If we created one table with out primary key column In form in search block have uwi value and top_depth value when i enter uwi and top_depth value then when i click search button then it will display all values in master block.
but here duplicate values r there.
SQL> select rownum,uwi,top_depth,base_depth,test_start_date from well_pre_header;
ROWNUM UWI TOP_DEPTH BASE_DEPTH TEST_STAR
---------- ---------------- ---------- ---------- ---------
1 100 453.05 458.08 09-SEP-10
2 100 200 288 23-AUG-00
3 1001 200 289 25-AUG-01
4 1001 200 201 24-MAY-87
if uwi = 1001 and top_depth=200 and i will click search button it should be display 3 record & when i click next button then it will show 4th record.
View 3 Replies
View Related
Sep 21, 2004
I am wondering how can I select data between two dates (ie from 08/12/2004 to today?)
Name DateS
Bob 08/11/2004
Tom 08/12/2004
Bill 09/21/2004 <-- today
return
Tom 08/12/2004
Bill 09/21/2004
View 4 Replies
View Related
Oct 8, 2012
I have an PL/SQL query which gives the data between the date interval submitted by the user.The problem is that i want all the date irrespective of it has data or not for eg: Let say date parameter is from 1-Jan-2012 to 5-Jan-2012
Now, in the database the available dates are:
1-Jan-2012
2-Jan-2012
5-Jan-2012
So as you can see here that dates 3-Jan-2012 and 4-Jan-2012 is not resulted out by the query. I want all the dates.
So the output should be like this:
1-Jan-2012
2-Jan-2012
3-Jan-2012
4-Jan-2012
5-Jan-2012
View 5 Replies
View Related
Sep 5, 2012
I have an athletics participation table that only has the relevant emplid and effective date field. There is no term field on the table. I'm trying to only select those emplid's where the max( effdt) for the emplid is between the begin and end date of the current term. I only want to select current athletes. I would much rather it be for the current academic year but it seems impossible. Why can't I use max(effdt) here?
sql Code
AND h.emplid IN(SELECT b.emplid FROM PS_ATHL_PART_STAT b
where max(b.effdt) between (SELECT term_begin_date AND term_end_date
from PS_TERM_TBL
[Code].....
View 2 Replies
View Related
Mar 23, 2010
SELECT DISTINCT a.emp_id, a.cal_id, TO_CHAR(a.ts_date, 'DD/MM/YYYY') tsdate, a.ts_date, 1 as days
FROM tmsh_timesheet a
INNER JOIN project b ON TO_CHAR(b.proj_id) = a.proj_id
INNER JOIN tmsh_ts_calendar c ON c.cal_id = a.cal_id
INNER JOIN (SELECT a.cal_id, a.emp_id, MAX(a.status) as status, a.create_dt, a.create_by FROM tmsh_stat_hist a
[Code]...
this query results
EMP_IDCAL_IDTSDATE
048283404/10/2010
048283502/11/2010
048283503/11/2010
048283504/11/2010
048283508/11/2010
i need the ts date to be in like this
04/10/2010
02/11/2010 - 04/11/2010
08/11/2010
View 16 Replies
View Related
Dec 19, 2012
I need to find the closest Date that matches a Particular Date. The Closest Date from the Group may be less than or greater than the Date I am trying to find.
I have two columns: VISIT_DATE and ACTUAL_DATE. The VISIT_DATE columns has many records with different dates while the ACTUAL_DATE column would only have one record per Student ID.
Here is an example of dates:
Visit Date Actual Date
==========================
01-APR-09 19-MAR-10
16-NOV-09 19-MAR-10
17-MAR-10 19-MAR-10
21-MAR-10 19-MAR-10
04-APR-11 19-MAR-10
15-JUN-11 19-MAR-10
19-SEP-11 19-MAR-10
24-FEB-12 19-MAR-10
The closest date to 19-MAR-10 are in fact 17-MAR-10 and 21-MAR-10. I would in that case need to pick up both records.
View 4 Replies
View Related
Sep 8, 2012
Is there a way I can generate a list of date using a simple SELECT statement to generate all dates between to date points? For example between SYSDATE AND SYSDATE-7?
One way I know is have a table and run PL/SQL script to put all dates in it and query that but was wondering if it can be achieve via SQL SELECT query at all without creating a table (querying DUAL).
View 5 Replies
View Related
Jun 1, 2010
If doing an insert into DATE type fields like below... how do I employ null indicator values with the TO_DATE sql to cope with NULL values for the End Date? I can test the NULLness of the pServiceRecord->itemTo value and set the indicator ind_dbToDate to -1 but I don't know how to incorporate this with the to_date syntax (if I can)?
EXEC SQL BEGIN DECLARE SECTION;
char dbFromDate[MAX_DATE_LEN];
char dbToDate[MAX_DATE_LEN];
short ind_dbToDate;
[code]...
View 2 Replies
View Related
Sep 11, 2010
In my server , already 10g r2 is installated , now am installaling 11r2, during this, at final stage while running root.sh , it will propmt to override 3 files, oraenv,dbhome .. etc under /usr/local/bin in solaris sys, as these files are already owned by 10g owner ,what i have to select (y or n)?
what if i select y? it will override three files
what if i select n? default option
View 2 Replies
View Related
Jan 26, 2011
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
[Code]...
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
View 2 Replies
View Related
Apr 20, 2007
i have a stored proc where i am selecting a value into a variable like so:
SELECT FUNCTION
INTO V_FUNCTION
FROM FUNCTION_TABLE
WHERE FUNCTION = P_INPUT;
Now, my problem lies in where there is no value returned (oracle will throw an error).
View 3 Replies
View Related
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 Replies
View Related
Sep 6, 2013
here is my query
select
max(PERIOD_DUE_DATE) , form_submission_id
from form_submission
group by
form_submission_id
but this returns all the records, I need only the max date along with its form_submission_id.In reality Its a complex query but to explain my problem I putting this simple query, how to select max(column) and column2 from table.
View 4 Replies
View Related
Jun 25, 2011
How do I select only last date for each contragentid? So for contragentid = 111270 it should be only '14.05.2010'.
select dd.contragentid,
decode(dd.ratingvalue,'PK1',1,'PK2',2,'PK3',2,'PSR',2,'UN4',2,'VVL',2,'BK',4,3) as ratingvalue from
(select 36 as contragentid, 'UN1' as ratingvalue, '25.02.2010' as ratingstartdate from dual
union all
select 111270 as contragentid, 'PK1' as ratingvalue, '26.11.2009' as ratingstartdate from dual
union all
select 111270 as contragentid, 'PK3' as ratingvalue, '14.05.2010' as ratingstartdate from dual
union all
select 111270 as contragentid, 'BK' as ratingvalue, '14.06.2011' as ratingstartdate from dual ) dd
where dd.ratingstartdate <= to_date('31.05.2010', 'DD.MM.YYYY')
Also I need to select all rows from second test case for those contragentid which absent in first case, it should be one statement for both cases.
select * from
(select 5 as contragentid, 2 as ratingvalue from dual
union all
select 111270 as contragentid, 1 as ratingvalue from dual ) hh
View 8 Replies
View Related
Mar 10, 2012
My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.
This is my table:
DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE;
CREATE TABLE BookingDM (
hNo NUMBER(3),
gNo NUMBER(5),
dFrom DATE NOT NULL,
[code]......
This is the first set I'm attempting to insert
hNo = 148
gNo = 11169
dFrom = 09/03/2009
dTo = 09/10/2009
rNo = 202
This is my attempt to insert the set:
SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202');
INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202')
*
ERROR at line 1:
ORA-01843: not a valid month
I need my dates to be in the format "MM/DD/YYYY".
View 4 Replies
View Related
Dec 7, 2011
I am trying to schedule a store procedure and wanted to remove some data from a table starting from a date and increase the delete by day until a specified date. I wrote the below function; but i can't get to work.
--Start
CREATE OR REPLACE FUNCTION remove_DateField return VARCHAR2 IS
i PLS_INTEGER;
startdate Date ;
endDate Date;
currentDate Date;
stopDate Date;
[code]....
View 8 Replies
View Related
Mar 1, 2010
I have emp table
Empno from_date to_date ScaleBonus_pct Salary
101 31/01/2010 14/02/2010Grade120,10,10,15,25,205000
Reqd. o/p
Emp nofrom_dateScaleEach_WeeK_Sal
10131/01/2010Grade11000(from 20 %X5000
1017/2/2010Grade1500(from 10 %X5000
10114/02/2010Grade1500(from 10 % X5000
10121/02/2010Grade1750(from 15 % X5000
10128/10/2010Grade11250(from 25 % X5000
1017/2/2010Grade11000(from 20 % X5000
we have to split the date by 7 days,should be on Sunday...and if we split by 7 days we have upto 31/01,07/02 and 14/02...but we have to generate 6 days because 20,10,10,15,25,20 (since comma separated value is 6 ).
View 8 Replies
View Related
Jan 26, 2011
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
1220.01.10
1321.01.10
1423.01.10
1526.01.10
1627.01.10
1729.01.10
1831.01.10
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
View 2 Replies
View Related
Oct 28, 2013
I am searching the simplest way for ad hoc MINUS.I do:
SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
View 6 Replies
View Related
Oct 9, 2007
there are some data in the table que_history (seqnbr is the key), e.g.
SEQNBR DN SL_TIME
20070927003668 (024)2272 AD182040 2007-9-27 15:15:00
20070928001343 (024)2272 AD182040 2007-9-28 9:55:14
20070928001624 (024)2272 AD182040 2007-9-28 10:30:06
20070928000910 (024)25672 AD000002 2007-9-28 9:06:59
20070928001288 (024)25672 AD000002 2007-9-28 9:49:13
20070923003834 (024)2585 AD210076 2007-9-23 17:15:13
20070923003890 (024)2585 AD210076 2007-9-23 17:23:54
20071001001593 (024)2589 AD000018 2007-10-1 11:54:39
20071003002814 (024)2589 AD000018 2007-10-3 16:53:52
20070923003320 (024)8831 AD000110 2007-9-23 15:24:39
I wanted to use this SQL to get the records ( dn is the same and the sl_time's interval is 600minutes) .
select A.* from que_history A,que_history B
where A.dn=B.dn and A.seqnbr<>B.seqnbr
and (A.sl_time-B.sl_time)*24*60 between -600 and 600
order by A.dn;
but the result is not the right.
View 3 Replies
View Related
Apr 13, 2012
I have an LOV on my form which holds a list of course units for a student to select and insert. However some units on the LOV must be COMPULSORY(not optional).... so i was wondering is there a way to have these auto selected from the LOV?
View 4 Replies
View Related
Jul 18, 2011
Is there a way to loop through a list of literal values.
For instance
create table car(
name varchar2(11),
passengers int,
price int
);
insert into car values ('fiat',1,1000);
insert into car values ('bmw',2,2500)
insert into car values ('ford',2,1500)
insert into car values ('ferrari',4,5000)
select
max(price)
from car
where passengers=1
How can i in a single query do this for where passengers = 1
then passengers = 2
then passengers = 3 etc
where i have a list of possible values for passengers.
Just to update I realise this can be done with
select
name,
max(price)
from car
where passengers in (1,2,3)
group by name
but in just wanted to know if there is a way of iterating through a literal list in tsql
View 1 Replies
View Related
Sep 10, 2013
Is there any way in PL/SQL to select the values from all columns of a table record into an array?
For example:
C1|C2|C3
0 |1 |2
v_array(0) value is 0
v_array(1) values is 1
v_array(2) values is 2
or
v_array(C1) value is 0
v_array(C2) values is 1
v_array(C3) values is 2
But i need to do this without mention the column names, something like: SELECT * FROM TABLE WHERE id=1 INTO v_array;
View 10 Replies
View Related
Sep 7, 2012
some sample data in my point geometry table.
every POLYID has two rows with NAME value, i need to select the two rows if NAME is same for a given POLYID.
example: POLYID 4351 has same name N, then i need to select two rows with PILYID 4351.
POLYID POINTID NAME
-----------------------------------------------------------------
4348 5763 N
4348 5764 F
4351 5741 N
4351 5756 N
4367 5721 M7
[Code]....
View 6 Replies
View Related
May 3, 2012
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
See attached file for creation script and data load.Each staff member is required to complete at least one task every three years. The source table contains an EID (aka User ID) and a date column for each task with a date of when the task was completed. If a task has never been started/completed the date value is "NULL".
If a row looks like this:
EID,DATE01,DATE02,DATE03,DATE04,DATE05,DATE06,DATE07,DATE08,DATE09,DATE10,DATE11,DATE12,DATE13
68,NULL,11/10/2009,5/3/2010,4/22/2012,NULL,NULL,4/14/2010,NULL,NULL,NULL,NULL,4/14/2010,4/14/2010
The the result set would look like this:
EID,MaxDate,Within_Last_3_Years
68,4/22/2012,'YES'
The result set will show the EID, date of latest task completed and if the task was completed within the last 3 years from given date (for example June 30, 2012).
View 4 Replies
View Related
Jun 13, 2011
I am having a following sql query:
Select
Product.code,
Customer.code,
Backlog.Date
SUM(Backlog.Qty)
From
Product,
Customer,
[code]....
Using this query i am getting following output::
Prod Cust Date Qty
A X 17-june 1000
A X 18-june 2000
A X 21-june 4000
B Z 11-May 200
B Z 15-May 500
C W 1- Dec 300
Out of these groups, i want to select qty for each product and customer, where date is maximum,that is following results::
Prod Cust Date Qty
A X 21-june 4000
B Z 15-May 500
C W 1- Dec 300
what condition/clause should i add in my query,tried a lot with having clause but on success.
View 17 Replies
View Related
