PL/SQL :: Date Range Sunday To Monday
May 16, 2013I would like to design a date range query where the beginning of the week is always sunday and the end of the week is always monday.
View 3 Replies
ADVERTISEMENT
SQL & PL/SQL :: Find Missing Date For A Year Excluding Saturday And Sunday
Feb 3, 2011I 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
PL/SQL :: Get Previous Sunday Through Saturday From Each Sunday
Sep 10, 2012I need to get the previous Sunday through Saturday from each Sunday. I want to run the report every Sunday and it will compare the previous dates from Sunday to Saturday.
View 3 Replies View RelatedPL/SQL :: Retrieve Date Range From 2 Search Input Date Fields
Aug 16, 2012I 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
PL/SQL :: Date Difference - Include Both Start And End Date In Range
Aug 8, 2013select to_date('28-FEB-2013') - TO_DATE('01-FEB-2013') FROM DUAL
gives me 27 days, what should I do to get 28 ? meaning include both the start and end dates in the range ?
SQL & PL/SQL :: Report With Date Range
Feb 4, 2013I have a cost data for effective date range as below.
MODEL_NOTIER_COSTEFFECTIVE_START_DATEEFFECTIVE_END_DATE
I3 3770 265 2/3/2013 3/31/2013
I3 3770 269 4/1/2013 5/4/2013
This data needs to be represented in a SQL report as below. The Date Range in the below i.e., Starts with FEB, by checking against the sysdate.. That is from sysdate it will display the Quarter data for 4 months as below.
MODEL NOFEB FY13MAR FY13APR FY13MAY FY13
I3 3770 265 265 269 269
Currently I am using a procedural logic to populate data into a different table in the above format. Is there any method to do with a single SQL using PIVOT. Below given is the table structure and Insert scripts.
CREATE TABLE ITEM_TAG(MODEL_NO VARCHAR2(50), TIER_COST NUMBER(13,4), EFFECTIVE_START_DATE DATE, EFFECTIVE_END_DATE DATE );
Insert statements
Insert into ADMIN_COSTPL_OWNER.ECAPS_ENTITLEMENT
(MODEL_NO, TIER_COST, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('I3 3770', 265, TO_DATE('02/03/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/31/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ADMIN_COSTPL_OWNER.ECAPS_ENTITLEMENT
(MODEL_NO, TIER_COST, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('I3 3770', 269, TO_DATE('04/01/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/04/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
SQL & PL/SQL :: Restriction In Date Range?
May 7, 2012I want my user to be restricted for entering duplicate time within two times.
create table asd(dt_frm date,dt_to date);
insert into asd VALUES(to_date('01-04-2012 08:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 10:00','dd-mm-yyyy hh24:mi'));
insert into asd VALUES(to_date('01-04-2012 09:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 11:00','dd-mm-yyyy hh24:mi'));
now in the second insertion I want to alert the entry user that 9am already falls in the saved record which is 8am to 10am and so that this record can't be saved.
Date Range Vs Interval Partitioning
Dec 16, 2010Other than the obvious to me, where interval partitioning creates partitions as needed. Is there any performance benefit from using interval partitions vs date range partitions.
One draw back for me is that developers do access the partition name in some of their queries, so if I use date range partitioning this will not break their code. I could not find a way to assign a name to a partition when using intervals, is this always system generated or can this be over-ridden.
I am running Oracle 11.1.0.7 soon to be running on 11.2.0.0
SQL & PL/SQL :: Display 1st Day Of Week Within Date Range
Jul 14, 2010How to display date every monday within date range
DEsc start date end date
XXXX 1/2/2010 31/10/2010
output
date every monday
SQL & PL/SQL :: Find Years Between Any Given Date Range
Apr 5, 2011I 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 RelatedSQL & PL/SQL :: Selection Based On Date Range
Dec 16, 2011Problem:
Our term (strm) is dictated by the term_begin_dt and term_end_dt dates but I want to keep selecting that term until 1 week before the next term opens and then switch to that term.
Basically, I don't want any gaps between a term.
Output:
select strm when sysdate is between term_begin_dt and term_end_dt (strm would equal 3943)
select strm until 1 week before the start of the next term (4027) (strm would equal 3943)
select strm when 1 week before term_begin_dt (strm would equal 4027)
Repeat for the next term and so on 12/16/2011
select strm
from term
where trunc(sysdate) between trunc(term_begin_dt) and trunc(term_end_dt)
Output: 3943
12/17/2011 - 01/01/2012
select strm
from term
where ?
Output: 3943
01/02/2012 - 5/4/2012
select strm
from term
where ?
Output: 4027
Repeat.
Test Case:
CREATE TABLE TERM
(
STRM VARCHAR2(4 BYTE),
DESCR VARCHAR2(20 BYTE),
TERM_BEGIN_DT DATE,
TERM_END_DT DATE
)
Insert into TERM
(STRM, DESCR, TERM_BEGIN_DT, TERM_END_DT)
Values
('3943', '2011 Fall Semester', TO_DATE('08/22/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('12/16/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TERM
[code].....
SQL & PL/SQL :: Count Data Within Date Range?
Nov 15, 2010I need to know the counts(into buckets) for each event_date for a given event_id.
I have 4 buckets that I'm trying to calculate
1. Past (from Today)
2. Today
3. > Today but within 3 days in the future
4. More than 3 days in the future
WITH event_data AS
(SELECT '1' event_id, SYSDATE - 1 event_date
FROM dual
UNION ALL
SELECT '1' event_id, SYSDATE
FROM dual
[code]....
Sample Output:
EVENT_IDPASTTODAYWITHIN_3_DAY_WINDOWOUTSIDE_3_DAY_WINDOW
1 111 1
2 102 0
SQL & PL/SQL :: Counting Records Within A Date Range?
Nov 1, 2011I am trying to write part of an SQL where it gives me a count of bookings in any 6 month period made from the first booking.Example of records
enquirynumberenquiryaddresssubjectcodebookingdate
613651 Burberry AvenueBCHR20/10/2008 07:00:00
613801 Burberry AvenueBCHR20/11/2008 07:00:00693021 Barberry AvenueBCHR07/09/2009 07:00:00
I am so far getting 3 as a count result based on SQL below. I want the count to return 2 (because its inside the 6 month range):
SELECT
ce1.enquirynumber,
ce1.enquiryaddress,
es1.subjectcode,
b1.bookingdate,
(SELECT count(b2.bookingdate)
[code]....
SQL & PL/SQL :: Replace Date Range In Query?
Aug 14, 2013I am trying to replace a date range in a query. Don't understand why it's not working, below is the dummy query for the same:
/* v_Employee:= 'select emp_name from employee_table where Join_Year=~Replace1~ and Release_Month <=~Replace2~';
v_Replace1:=2013;
v_Replace2:=7;
pos := 1;
WHILE (INSTR(v_Employee, '~', 1, pos) <> 0) LOOP
IF (pos = 1) THEN
[code]....
Since actual code is like CLOB type, so I have not provided the same. Here after executing this query i found that v_Employee query is getting replaced by v_Replace1 at both of his position i.e "select emp_name from employee_table where Join_Year=2013 and Release_Month <=2013"
But my actual result should: "select emp_name from employee_table where Join_Year=2013 and Release_Month <=7"
SQL & PL/SQL :: Compare Date And Time Within Certain Range?
Nov 28, 2012I'm trying to compare a date and time within a certain range.
If the sysdate date/time range falls within the range of the values within the database tables then show a "Yes", otherwise, show a "No". The date works but the time doesn't seem to work. Maybe you can't use a "between" operator for time?
CREATE TABLE REGISTRATION
(
EARLY_REGISTRATION_START_DATE DATE,
EARLY_REGISTRATION_END_DATE DATE,
EARLY_REGISTRATION_START_TIME DATE,
EARLY_REGISTRATION_END_TIME DATE
[code]....
Range Partitions On A Date Column
Jul 7, 2012I have a table which has 2 range partitions on a date column currently.
CREATE TABLE TABLEA (
RUN_TIME INT NOT NULL
)
PARTITION BY RANGE (RUN_TIME)
(
PARTITION DATAONE VALUES LESS THAN (20110101000000) TABLESPACE SPACE1
PARTITION DATATWO VALUES LESS THAN (MAXVALUE) TABLESPACE SPACE2
);
I am planning to drop one partition i.e DATAONE. So table will have one partition left for MAXVALUE. Does it make sense to have a partition with MAXVALUE? Isn't it same as TABLEA in terms of number of records? TABLEA is also in tablespace SPACE2. Should i remove partition DATATWO also? If i have to what is the best way to move all DATATWO records to base table TABLEA?
SQL & PL/SQL :: Group By Count And Date Time Range
Feb 3, 2011We have a table with timestamp column and having millions of records.We want to create a materialized view or query, which can give count based
-on some group by columns from table and
-group by on condition (if count > 1000) and
-group by on condition (if timestamp range for that group is > 1hr)
SQL & PL/SQL :: Query Using Analytical Functions-date Range
Nov 30, 2010I have a table which has the attached data.
Sample data is here
LOGON_DATE NUMBER_OF_LOGINS
11/28/2010 02:00:001
11/28/2010 03:00:001
11/28/2010 04:00:002
11/28/2010 06:00:004
11/28/2010 07:00:002
11/28/2010 08:00:003
11/28/2010 09:00:006
[Code] ........
I am trying to do a report like this.
Date PeakUsersBetween6AMand6PM AVGUsersBetween6AMand6PM PeakUsersBetween6PMand6AM AVGUsersBetween6PMand6AM
Output should be
11/28/2010 25 11 49 27
11/29/2010...
I am using analytical function to do this, It throws an error range cannot be used for dates.
SQL & PL/SQL :: Selecting Specific Date / Time Range?
May 8, 2013I want to select a specific date/time range in a query. I want to select from 6 AM yesterday through 6 AM today. I know that CURRENT_DATE - 1 will give me yesterday, and I can search between that and the current_date. However, how do I incorporate the specific time in the query?
View 4 Replies View RelatedSQL & PL/SQL :: Date Query - Input Year In Specified Range
Dec 26, 2012Getting problem with below query.
select to_date('30-DEC-00','DD-MON-YYYY') from dual
Error starting at line 1 in command:
select to_date('30-DEC-00','DD-MON-YYYY') from dual
Error report:
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range
SQL & PL/SQL :: Conditional Date Range Using CASE Statement In WHERE Clause
Mar 28, 2011Is it possible within a CASE statement to put conditions on the date range that I want to pull? IE: am versus pm. The query has to pull specific time ranges for an AM run versus a PM run.
.....
FROM
table
WHERE
CASE
WHEN TO_CHAR(SYSDATE,'AM') = 'AM'
THEN table.date BETWEEN TRUNC(SYSDATE) AND SYSDATE
ELSE table.date BETWEEN TRUNC(SYSDATE+12/24) AND SYSDATE
Forms :: Search Data On Basis Of Date Range?
Sep 13, 2010i create form on which 3 window, 3 Canvas, 3 blocks are there.
1)first block contain 3 items from_date and to_date(nondatabase)and find button
2)second block contains 3 items segment1, quantity and payment_terms
when i enter 2 dates and click on find button 2nd window open. segment1 and quantity between this 2 dates comes from base table like
select segment1,quantity
from po_lines_all pll, mtl_system_items mts
where pll.ITEM_ID=mts.INVENTORY_ITEM_ID
and creation_date between :FROM_DATE and :TO_DATE
and payment_terms should enter manually
3)2nd block contains summary button when i click on it, it save segment1, quantity, payment_terms and open new window which contain segment1 ,qty, payment_terms and when i click on summary button data comes from custom table which group clause means
suppose
item qty terms
a 2 2days
a 3 2days
b 3 3days
then on third form i shows like this
a 5 2days
b 3 3days
i done all this things but problem is that
suppose once data between 21-aug-2010 and 24-aug-2010 comes on second form , i enter payment_terms and save it. sumaary form also run properly but if another user enter date between 22-aug-2010 and 30-aug-2010 then it will shows data between 21-aug-2010 to 24-aug-2010 from custom table i.e with payment terms as earlier it save and from 25-aug-2010 to 30-aug-2010 it shows data from base table
like
item qty terms date
a 2 2days 21aug
a 3 2days 22aug
b 3 3days 24aug
b 4 NULL(ENTER MANUALLY) 26aug
c 5 NULL 27aug
PL/SQL :: Selecting Missing Date Range With Previous Records Value
Jun 8, 2012SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - ProductionSET DEFINE OFF;
[code]....
10 rows selected.I want the output like as follows, all those missing date i need to carry on the last one's number
NBR_OF_S NBR_OF_C S_DATE
---------- ---------- ---------
34 40 01-MAY-12
27 29 01-APR-12
27 29 01-MAR-12
21 23 01-FEB-12
21 23 01-JAN-12
21 23 01-DEC-11
[code]....
The date value I have created for this sample is monthly, based on the condition the data value I may need to generate weekly also. That's Monthly or weekly either one.
PL/SQL :: Query To Find First And Last Call Made By Selected Number For Date Range
Apr 27, 2013create 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]....
PL/SQL :: How To Get Monday Of Every IW Week In A Year
Jun 29, 2012I had to get a query to give me all the Mondays of a IW week in a year. I think as per the IW weeks there are around 52.1 weeks in a year. So basically, I want the starting Monday of every week. I would then store those values in a variable and use them in APEX as column headers. Online I saw some samples not related with this, but where looping was done using the all_objects. Not sure if that is the right approach.
The problem is since this is not stored in any table, how can I loop like 52/53 times such that the output shows all the Mondays (date format) for a given year. Basically, the output would be 52/53 rows with a date(Monday of the week).
PL/SQL :: Change Monday Like First Day Of Week
Oct 18, 2012I need to change Monday like first day of week and not Sunday.
select to_char(to_date('01-01-2012'),'w') from dual
Result: 1
select to_char(to_date('02-01-2012'),'w') from dual
Result:1
and the idea is that 02-01-2012 must be the second week
Reports & Discoverer :: Week Day Starts From Monday
Feb 20, 2012I have m display financial year calender(April-2011 to March 2012) and i want to display monday as a first week day.
View 3 Replies View RelatedSQL & PL/SQL :: Create Normal Partition Range On Date And Sub Partition List On Batch ID
Mar 17, 2011I Know we can create dynamic partitions on table in oracle 11g. Is it possible to create normal partition and sub partition both dynamically.I have to create Normal partition range on date and sub partition list on Batch ID (varchar).
View 3 Replies View RelatedSQL & PL/SQL :: How To Schedule Job To Run Every Sunday At 12 Pm IST
Jun 11, 2013I want to schedule a job which will run evry Sunday at 12 P.M IST.I have written the below script.
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SERVICE_SAL_FS.SAL_MESSAGE_BUFFER_PURGE_JOB'
,start_date => SYSTIMESTAMP
[code]...
But the server in US.
SELECT sysdate from Dual;
6/11/2013 3:58:58 AM
--But the time in India is 02:28 PM
How to convert the timezone to IST. how to schedule the job to run evry Sunday at 12 P.M IST.
SQL & PL/SQL :: Returning Sysdate Even Run On Sunday
Apr 4, 2010I have following code. It is always returning sysdate even run on Sunday.
declare
vDate date;
begin
if to_char(sysdate,'Day')='Sunday' THEN
vDate := sysdate-3;
[Code]...
if there is something missing or wrong.