i am sure it may be wrong because it doesn't mention year however i have been told to get a list of all students who enrolled in MAY. and i have tried doing
select * from table where enrolDate > to_date('01-05','DD-MM') and enrolDate < to_date('31-05','DD-MM') and it doesnt work (says no rows selected when in fact there are some rows!
i can do it fine when using YEAR ie '01-05-2000','DD-MM-YYYY' another problem.
I am trying to do this:
Select to_char(to_date(01012008,DD-MM- YYYY),DAY) from dual;
and it is giving me the error however if i do this:
Select to_char(to_date(16012008,DD-MM-YYYY),DAY) from dual;
I am trying convert number value in date. I know somewhere I doing mistake. But I cant get it.
Here is my Partial Code
create or replace PROCEDURE "REPORT_ARTICLEMOSTVIEWED2" (
[Code]....
Error starting at line 5 in command: EXEC REPORT_ARTICLEMOSTVIEWED2(null,null,null,null,:RC) Error report: ORA-01858: a non-numeric character was found where a numeric was expected ORA-06512: at "IIS_ORACLE_11GR2_LIVE.REPORT_ARTICLEMOSTVIEWED2", line 22 ORA-06512: at line 1 01858. 00000 - "a non-numeric character was found where a numeric was expected" *Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model. *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation. RC
How do I put condition for Null value in this procedure And set dateTo = sysdate if v_day,v_month,v_year are null.
I got a table with a date-typed column called: "Birth_date", and I wanna write a function that retrieves all the records that "got a birthday" this week.
in order to check weather a record got a birthday this week I need to check only the day & month of "Birth_date" that BETWEEN (sysdate-7) AND (sysdate),
In oracle query can i want find out how many day wise count for a year days (for example how may sundays, mondays, tuesdays, wednesdays ,thursdays,fridays,saturdays) in a given year (we can give the start day of the year and the end day of a year).
example ---------- jan sun-5 mon-4 tue-5 wed-5 thu-5 fri-4 sat-5 feb ------------do--------------------------------- like this for all 12 months at a single query.
I have a table in which years are stored in the form '2008/2009'. This is making it very difficult for me to do any calculations on that field and so I was wondering if there was a way to change the years (in a query and not in the actual table) so that if the year was '2008/2009' I would have just '2009'.
select inv_dtime where inv_dtime between trunc(sysdate,'YYYY') and last_day(trunc(sysdate,'YYYY')) from temp;
I want to have the start date of the year and end date of the year in my condition. like between 01-JAN-2012 AND 31-DEC-2012. I tried the above but it doesn't come.
( select 0001 item_code, to_date(1997,'yyyy') Yr from dual union all select 0002 , to_date(1998,'yyyy') from dual union all select 0003 , to_date(1999,'yyyy') from dual union all select 0004 , null from dual union all select 0005 , null from dual) select count(Yr) from test group by yr
COUNT(YR)
1 1 1 0
But I want the completed year count and non completed year count The non completed year count should be 2 as there are two null How I would acheive?
Query 1: SELECT DISTINCT YR FROM( SELECT TO_CHAR( ADD_MONTHS (TRUNC (TO_DATE('01/01/2007' ,'DD/MM/YYYY'), 'YYYY'), 1*LEVEL -1) , 'YYYY') YR FROM Dual CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(:to_dt ,'DD/MM/YYYY'), TO_DATE('01/01/2007' ,'DD/MM/YYYY')) + 1 ORDER BY YR )
Gives the Output as YR **** 2007 2008 2009 2010 2011
Query 2 ******* SELECT DISTINCT MONTH FROM( SELECT TO_CHAR( ADD_MONTHS (TRUNC (TO_DATE('01/01/2007' ,'DD/MM/YYYY'), 'MM'), 1*LEVEL -1) , 'MM') MONTH FROM Dual CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(:to_dt ,'DD/MM/YYYY'), TO_DATE('01/01/2007' ,'DD/MM/YYYY')) + 1 ORDER BY MONTH ) Gives the Output as
I 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).
Begin insert into employee_rev values(111, 'DEC-09', '31-DEC-2009', '01-APR-2009', 1300); insert into employee_rev values(111, 'JAN-10', '31-JAN-2010', '01-APR-2009', 1000); insert into employee_rev values(111, 'FEB-10', '28-FEB-2010', '01-APR-2009', 800); insert into employee_rev values(111, 'MAR-10', '31-MAR-2010', '01-APR-2009', 1000); insert into employee_rev values(111, 'APR-10', '30-APR-2010', '01-APR-2010', 1000); insert into employee_rev values(111, 'MAY-10', '31-MAY-2010', '01-APR-2010', 1100); insert into employee_rev values(111, 'JUN-10', '30-JUN-2010', '01-APR-2010', 2100); End;
I also need a YTD (Year to Date) field at the last which is sum of current month revenue_amt and sum(revenue_amt) for previous months for a particular financial_year_date.
The output should be:
Person_ID Month End Date Year Date Rev YTD 111 Dec-09 31-Dec-09 1-Apr-09 1300 1300 111 Jan-10 31-Jan-10 1-Apr-09 1000 2300 111 Feb-10 28-Feb-10 1-Apr-09 800 3100 111 Mar-10 31-Mar-10 1-Apr-09 1000 4100 111 Apr-10 30-Apr-10 1-Apr-10 1000 1000--change in financial year 111 May-10 31-May-10 1-Apr-10 1100 2100 111 Jun-10 30-Jun-10 1-Apr-10 2100 4200
I guess this should be achievable using some analytical functions, but I am unable to get the desired output.
The value is an aggregate Year to Date Figure And I was wondering what the best method of splitting this data out into a Monthly Figure so that it would look like below:
Year Month Mth Value 2011 01 15 2011 02 11 2011 03 8 2011 04 9
1. Year's first week starts with first Sunday of the year. (6th Jan 2013 will be the starting week (week 1) in Year 2013) 2. 2nd Jan 2013 will be the last week of the previous year i.e. 2012 (either 52th or 53rd week)
At many place I found the below solution:
select to_char(to_date('01-JAN-2008','DD-MON-YYYY')+1,'IW') week_number from dual;
But its not working for the given dates (2nd Jan 2013, which should fall in the last week of 2012, and 6th Jan 2013, which should be the starting week of 2013).
IF THE USER ENTER parameter for date(yymm) in a report as 201301
select vndr#,sum(net_sales_value) from mnthly_sales_value where vndr# = 111 and yymm = :yymm group by vndr#;
but I need result of 3 months, but in my table data is stored for one month so how to get 3 months then again 3 months so on for Q1,Q2,Q3,Q4
yymm between 201301 and 201303 and yymm between 201304 and 201306 and yymm between 201307 and 201309 and yymm between 201310 and 201312 in the same query
I currently have around 560 rows of bad data where
GL_Encumbered_Date and TO_CHAR(PRD.GL_ENCUMBERED_DATE,'YYYY' display correctly.
The problem lies in
PRD.GL_CANCELLED_DATE which displays correctly (example: 30-APR-10) but TO_CHAR(PRD.GL_CANCELLED_DATE, 'DD-MON-YYYY') for the same row displays as 30-APR-0010.
I have separated out the incorrect year with:
to_char(prd.gl_cancelled_date,'YYYY') where each of the 560 rows display 0010 instead of 2010. I am unsure if arithmetic with dates is allowed within SQL- but am curious if the operation date + number will result in a date.
If this is so, how could one go about taking 0010 as a date and add 2000 to create 2010 for: PRD.GL_CANCELLED_DATE.
insert into sample values('1','01-JAN-2005'); insert into sample values('1','1-JAN-2006'); insert into sample values('2','2-JAN-2007'); insert into sample values('3','4-JAN-2005'); insert into sample values('4','11-JAN-2004'); insert into sample values('3','2-JAN-2005'); insert into sample values('2','12-JAN-2006'); insert into sample values('1','21-JAN-2005'); insert into sample values('2','11-JAN-2005'); insert into sample values('7','01-JAN-2005'); [code]....
