SQL & PL/SQL :: Date Diff (Month Not Day) Equivalent In Oracle

Jun 16, 2010

I see month aging buckets in the Oracle Application I am using (Keystone time and billing). I need to do a query in directly in Oracle (Toad front end to Keystone database) using month aging buckets . I use the following in Access that matches the results in Oracle, but I need to work directly in Toad because I want to avoid having to bring over all the dates when I want to summarize by buckets.:

2-4 Months: Sum(IIf(DateDiff("m",[invoice date],forms!Reporting_Standard!txtlastdayofmonth) In (2,3),[Outstanding Gross],0))

I know about getting the difference between two dates and that works fine for day aging buckets, but I need months, which can deal with months that are different sizes.

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Required Data For Month To Date And Year To Date?

Apr 30, 2012

I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.

View 3 Replies View Related

SQL & PL/SQL :: What Would Be The Sybase Str Equivalent In Oracle

Mar 4, 2010

What would be sybase str(approx_numeric [, length [, decimal] ]) equivalent in oracle.

Sybase call :

select str(10000.5555, 6, 3) as Test.

Tset
-------
10001

View 4 Replies View Related

SQL & PL/SQL :: Left Function Equivalent In Oracle 9i

Dec 22, 2006

Is there is an equivalent to the 'LEFT' Function of SQLServer in Oracle9i?

View 10 Replies View Related

PL/SQL :: Find Equivalent Of Mysql_data_seek In Oracle

Oct 23, 2012

"php 5.3.x + oracle 11g R2 XE 11.2.x.x"...I'm quite new to oracle (2-4 months) and php(2 weeks).I have one query with results that needs to be reused in several parts of my website. I can't seem to find the equivalent of the mysql_data_seek in Oracle. I wanted to reset the cursor/pointer of oci_ fetch ($result) so that I can scroll the result again.So far this are what I have come up:

A. On first fetch put the the results in a php array and call the array later on.
B. Do the query again.
C. Keep on looking for a mysql_data_seek equivalent and fail.

I'm leaning towards option 'A' but I just wanted to consult the experts.

View 4 Replies View Related

PL/SQL :: How To Get Min Date Of Every Month For Six Months

Mar 25, 2013

I have data like this.

Process_date SEQ_No
------------- ---------
16-MAR-13     733
09-MAR-13     732
02-MAR-13     731
24-FEB-13     730
16-FEB-13     728
09-FEB-13     727
02-FEB-13     726
26-JAN-13     725
21-JAN-13     724
12-JAN-13     723
05-JAN-13     722
29-DEC-12     721
24-DEC-12     720
15-DEC-12     719
08-DEC-12     718
03-DEC-12     717
22-NOV-12     716
17-NOV-12     715
10-NOV-12     714
03-NOV-12     713
29-OCT-12     712
23-OCT-12     711
13-OCT-12     710
05-OCT-12     709
28-SEP-12     708
22-SEP-12     707
15-SEP-12     706
08-SEP-12     705
01-SEP-12     704

every month admin will refresh actual data table and automatically this above table will update with unique seq_no and process_date.

I need to extract min date of every month(First refresh of last 6 months - excluding current month) and also seq_no related to that month so using joins(using seq_no - that is available in main table) i can combine actual data.

I need result like:

02-MAR-13     731 ( I don't need MAR as it should not take current month data)

so i need final result like below:

02-FEB-13     726
05-JAN-13     722
08-DEC-12     718
03-NOV-12     713
05-OCT-12     709
01-SEP-12     704

View 8 Replies View Related

SQL & PL/SQL :: How To Get Java Nano-time Equivalent In Oracle

Dec 11, 2011

how to get java nanotime equivalent in oracle

View 2 Replies View Related

Specify Month Start And End Date In Query?

Oct 27, 2008

I want a monthly report where the month wise sum of qty should be displayed in a row for last 12 months. I need to specify the month start date and end date in the query to pick the sum for the particular month. How can i do it in a SQL query?

SUM(decode(to_char(t1.trx_date,'mm/rrrr'),
to_char(add_months(SYSDATE,
-1),'mm/rrrr'),
nvl(t2.quantity_invoiced,
0))) AS qty_01
from t1,t2
where t1.col1=t2.col1

instead of sysdate i have to use start and end of month. Also i am using group by clause on some columns.

View 1 Replies View Related

Date Search By Last Week / Month?

Nov 2, 2011

I have a create a report for our dept with certain criteria. So far what I need is a report that shows last weeks numbers or sign ups. I can't get the date search or between to work.

What I need is one for month end and one for last 7 days.

here's what I have so far.

select ap.name

to_char(ap.opendate,'MM-DD-YY') "Open Date"

from [databasname]

where ap.opendate between databasename-7 and databasename-1

I really don't want to change the date myself I want the system to know when the 7 days or month is.

View 4 Replies View Related

SQL & PL/SQL :: Date Format With Zero Excluded From Month

Sep 20, 2010

i have a requirement where i have to exclude the zero from the month e.g jun=06 but i require 6 only not zerodec=12 i require 12

Do we have any format in oracle which support this.

View 3 Replies View Related

SQL & PL/SQL :: ORA-01839 - Date Not Valid For Month Specified?

Nov 1, 2013

our web application has a field DATETO which has no validation (end date). So user can put garbage there. In this field should be SYSDATE + max. 2 years. But also SYSDATE + 1 month OK.

I need to make a script that sets for example "31.12.9999" to null. Because so long end date is non sense.

SQL> SELECT EXTRACT (YEAR FROM (TO_DATE (DATETO, 'DD.MM.YYYY')))
FROM XXX_USER_JC_ORDERID
WHERE DATETO IS NOT NULL;
ERROR:
ORA-01839: date not valid for month specified

no rows selected

Littlefoot writes about ORA-01839 "It usually happens when invalid dates are used, such as 30th of February"[URL]..

I don't see invalid date???

SQL> SELECT DATETO
FROM XXX_USER_JC_ORDERID
WHERE DATETO IS NOT NULL;
DATETO
--------------------------------------------------------------------------------
23.09.2013

[code]...

DATETO
--------------------------------------------------------------------------------
31.12.2014
31.12.2016
31.12.2013
31.08.2014

[code]...

View 7 Replies View Related

PL/SQL :: Calculate Financial Month From Date

Mar 28, 2013

I need a pl/sql function to calculate the financial month from an date... For eg. If i enter input date as '21-sep-2012' the function should return me the output value in months as 6. so it should calculate from april to september of that year...

View 13 Replies View Related

Get Month Number From Date Format

Oct 31, 2012

I am trying to get the month number from this date format '19-OCT-12' . when i try to run the below statement i am getting an error like "not a valid month"

select to_date('19-OCT-12','MM') from dual

i need to get value as 10

View 4 Replies View Related

Split Date Column Into 4 Month Periods

Feb 11, 2011

I have a table having a code column A and a date column D1 which represents the days when the code was inserted. I can have more then 1 date for a code.

The idea is to create another column (an id) which will correspond to all the inserted days of a code for a period of 4 month. if, after 4 month of the first insert, another records appears, then another id will be generated for my code. If a code has the date included in one of the intervals existing already, then it will just receive the corresponding id.

Example:
id code date
id1 cd1 01/01/2010
id1 cd1 04/03/2010
id1 cd1 22/04/2010
id2 cd1 27/05/2010
id2 cd1 21/06/2010

If a cd1 appears now on 22/05/2010, then i have to give it id2.

View 3 Replies View Related

SQL & PL/SQL :: Date - How To Get Past 12 Months From Current Month

Dec 17, 2011

I need to get the past 12 months from the current month

for example

input:

march2010

output:

apr2009
may2009
june2009
july2009
augest2009
september2009
october2009
november2009
december2009
january2010
february2010

View 23 Replies View Related

SQL & PL/SQL :: How To Sort Date By Month (JAN) And Year (2009)

Jun 4, 2010

I've done this once before, but can't seem to find the sql.

How can I sort by month and year on a column called ex: TEST_dATE

JAN 2007
FEB 2007
APR 2008
SEP 2009
OCT 2009
FEB 2010
JUN 2010

View 15 Replies View Related

SQL & PL/SQL :: Find Out Date Difference In Day-month-year?

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

PL/SQL :: Insert Only Month And Year Instead Of Full Date

Oct 8, 2012

select b.penjara_id, p.penj_lokasi, a.no_daftar, b.episod, b.nama1,to_char(a.trkh_mula_prl,'dd/mm/yyyy') as trkh_mula_prl, to_char(bulan_proses,'mm/yyyy') as bulan_proses,
        b.epd, b.lpd
from prl_daftar_proses a, senarai_pesalah b, penjara p

where a.no_daftar=b.no_daftar
and a.episod=b.episod
and b.penjara_id = p.penjara_id
and a.setuju_jplp is null
and a.bulan_proses between to_date(:FROM,'dd/mm/yyyy') and to_date(:TO,'dd/mm/yyyy')
order by b.penjara_id, a.bulan_proses,a.no_daftarHi,

how i can insert only month and year from the value that have full date in the database?

for example,the date is 09/18/2012, but i just want to insert 09/2012 as parameter. If i want to insert only one parameter, i can do that..But i have problem when I want to insert two parameters..

View 16 Replies View Related

SQL & PL/SQL :: Calculations - Get Same Month From Previous Year Of Selected Date

Sep 20, 2011

i have a requirement to create a query which gets the same month from the previous year of the selected date .

Example : i select SEP-2011 ( as sysdate )

it will return the SEP-2010 as well .

here is the code which works fine with sysdate, dual :

SELECT
TO_CHAR(ADD_MONTHS(SYSDATE,-12),'MON-YYYY') TMLY, TO_CHAR(SYSDATE,'MON-YYYY') TMTY FROM DUAL;

in my schema table Ive got a filed named PERIOD_NAME (varchar2) , which holds date values in ( Mon-YYYY ) format (e.g Sep-2011)

am unable to apply the above code on it , i guess its the data type pf the field .

View 5 Replies View Related

PL/SQL :: Find Invalid Date And Month Not Match With Calendar?

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

Application Express :: Date Formatting - Not A Valid Month

Apr 29, 2013

I am using the cloud version of APEX for a college course. The database script I am trying to load has dates formatted in "DD-MON-RR" and I receive 'not a valid month' and 'a non-numeric character was found where a numeric was expected'. Is there a workaround in APEX for this? I'm trying to avoid changing thousands of lines of data.

View 4 Replies View Related

SQL & PL/SQL :: Report Deign To Generate Month Wise Sale - Date Format

Sep 23, 2013

how to write pl/sql to create date o/p like .

since we wnat to design pl/sql report wich will generate monthwise sale from 01jan2009 to 01-sep-2013.

we have created funtion wich will accept argument as employee no, from date and to date.

from_date to_date
01-JAN-2009 01-FEB-2009
01-FEB-2009 01-MAR-2009
01-MAR-2009 01-APR-2009
01-APR-2009 01-MAY-2009
01-may-2009 01-JUN-2009
01-JUN-2009 01-JUL-2009

View 4 Replies View Related

SQL & PL/SQL :: How To Calculate Time Diff Between LAST_ANALYZED Dates

May 27, 2011

Oracle Version = 8.1.7.4

How to change the SQL below to include a calculated datetime diff between two consecutive dates?

I believe I should be using what SQL gurus will call hierarchichal/analytical queries? Any good website/link to learn hierarchical/analytical queries.

select last_analyzed, monitoring
from dba_tables where owner = 'TEST'

LAST_ANALYZED MON
-------------------- ---
24-MAY-2011 18:25:05 YES
24-MAY-2011 19:15:34 YES
24-MAY-2011 22:21:33 YES
24-MAY-2011 23:10:42 YES
24-MAY-2011 23:51:10 YES
25-MAY-2011 01:43:56 YES
25-MAY-2011 02:39:11 YES
25-MAY-2011 03:10:26 YES
25-MAY-2011 04:15:15 YES
25-MAY-2011 04:23:26 YES
28-MAY-2011 03:58:02 YES
28-MAY-2011 03:58:11 YES
28-MAY-2011 03:58:11 YES
28-MAY-2011 03:58:12 YES
28-MAY-2011 03:58:13 YES
28-MAY-2011 04:00:16 YES
28-MAY-2011 04:04:24 YES
28-MAY-2011 04:04:27 YES
28-MAY-2011 04:04:33 YES
28-MAY-2011 04:04:36 YES

View 2 Replies View Related

Server Utilities :: Performance Diff In Exp And Expdp?

Jun 4, 2010

I export a table using exp utility it take 30 mins to complete the export.The same i have done in expdp utility it take 10 mins to complete the export.

How it happens?

View 3 Replies View Related

SQL & PL/SQL :: How To List Data Dynamically Month Wise Picking Month From The Same Table

Jun 4, 2013

I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.

1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);

2. List table content:
select * from T1;

account_no area_code bill_month consumption

Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......

3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12

Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182

With the conventional query I hope this is impossible,

View 2 Replies View Related

SQL & PL/SQL :: Verify Current Date Is Greater Than 15th Of Current Month

Sep 22, 2010

I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.

View 5 Replies View Related

SQL & PL/SQL :: Hexadecimal Equivalent

Jun 16, 2011

I need to get the output as ú (Lowercase U-acute) using oracle sql.

SQL> select UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('<x>')) from dual;

where <x> represents the hexadecimal equivalent for the character 'ú' ,

View 3 Replies View Related

PL/SQL :: Top N Percent Equivalent

Jul 16, 2013

what is the equivalent of Top n Percent in Oracle sql 11g. Here is my requirement:

I have to find stores contributing top 20% of sales: Store Sales PercetageABC200(200/380)*100=52%XYZ100(100/380)*100=26%PQR50(50/380)*100=13%dddd20(20/380)*100=5%rrrr10(10/380)*100=2% 

In the above example I have to get only store ABC as this store alone is contributing more than Top 20%If I change the requirement to Top 70% I have to get store's ABC and XYZ.

View 6 Replies View Related

PL/SQL :: Code Erases Previous Month Record While Updating Current Month Record

May 16, 2013

Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57                         
2013             February     loc1          47960          3660               53                      24
2013             March          loc1          55007          78               57                      28
2013             April          loc1          54345          72               58                  77The code:

UPDATE missing_volume g

[Code]....

The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57                         
2013             February     loc1          47960          3660               53
2013             March          loc1          55007          78               57
2013             April          loc1          54345          72               58                   77

why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).

View 5 Replies View Related

Upgrade :: 10g Companion CD Equivalent In 11g?

Sep 25, 2013

I am upgrading from 11.2.0.1 to 11.2.0.3 (64 bit) on Windows (64 bit)  I planned to install Oracle 11.2.0.3 on new home and i am not sure wheteher i have to install:  Oracle Gateway and Oracle Examples on the 11.2.0.3 new home.  I am not using any non - oracle so i may not need Oracle Gateway.  I am not sure about examples media . Does it have only examples to learn (or)will it have any optional components which we may require (like companion CD in 10g)?    What about the ODBC and com components will it be installed with the database media itself?  After DB upgradation is it possible to install oracle gateway/examples on the same home?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved