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.
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.
"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.
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)
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.
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.
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
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...
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"
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.
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..
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
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.
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.
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.
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.
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.
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).
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?
