SQL & PL/SQL :: Why To_Date Not Returning Whole Year

Jan 16, 2011

I run following query.

select to_date(sysdate,'DD/MM/YYYY') Dte FROM DUAL

Result is

Dte
--------
01/17/0011

why it is not returning '01-17-2011'?

View 3 Replies


ADVERTISEMENT

To_date - Using Just Dd / Mm And Not Year

Jun 18, 2008

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;

View 3 Replies View Related

SQL & PL/SQL :: Getting Stuck With To_date Function / ORA-01858

Apr 22, 2011

I always get stuck with to_date function.

I keep getting errors when I run this query.

select * FROM table1 WHERE AS_OF_DATE = to_date('9/30/2010 12:00:00 AM','YYYYMMDD HH24:MI:SS')

Error code is : ORA-01858

View 12 Replies View Related

SQL & PL/SQL :: Passing Variable Into TO_DATE Function

Apr 14, 2011

select to_date('13:14:00', 'HH24:MI:SS') FROM DUAL;

what is output of this?

and why this result is coming?

View 32 Replies View Related

SQL & PL/SQL :: Passing Variable Into TO_DATE Function?

Aug 10, 2010

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.

View 9 Replies View Related

PL/SQL :: To_date In Insert Statements In Cursor

Jan 2, 2013

I was wondering if I could use a to_date in an insert statement in a cursor.Would the following piece of code be valid?

create table main_table
( eff_date date,
version varchar2 (5),
user1 varchar2(10));
[code]....

View 11 Replies View Related

SQL & PL/SQL :: Same Day At Last Year

Apr 20, 2011

my propble is supose if today is Tuesday 01�32011 which is week 16 of this year i need to get same day tuesday on week 16 of last year

View 12 Replies View Related

SQL & PL/SQL :: Date Without Year

Sep 27, 2010

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),

but I don't know how..

View 39 Replies View Related

Day Wise Count For A Given Year

Jul 2, 2011

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.

View 3 Replies View Related

Changing Year Format

Nov 23, 2009

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'.

View 4 Replies View Related

SQL & PL/SQL :: Start And End Date Of The Year

Mar 1, 2012

I have a sql like this

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.

View 4 Replies View Related

SQL & PL/SQL :: Max And Min Values Of Month And Year

Jun 25, 2010

can we take the maximum and minimum value of month and year

View 7 Replies View Related

SQL & PL/SQL :: Count For Null Year

Feb 24, 2010

with test as

( 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?

==============

View 3 Replies View Related

SQL & PL/SQL :: Year And Month Query

Mar 20, 2011

I have two Queries.

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

MONTH
***
01
02
03
04
05
06
07
08
09
10
11
12

I want to combine these two. I need the output as

2007-01
2007-02
2007-03
....
...

View 8 Replies View Related

PL/SQL :: How To Get Monday Of Every IW Week In A Year

Jun 29, 2012

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

View 10 Replies View Related

PL/SQL :: To Find Leap Year?

Aug 23, 2012

How to find given year is leap year or not, if leap year i want the feb month no : of days.

View 5 Replies View Related

Interval Year To Month Parameter

Mar 20, 2008

I have a procedure that has a 'INTERVAL YEAR TO MONTH' parameter. What value do I pass to this parameter?

View 1 Replies View Related

SQL & PL/SQL :: Get Date From Year / Week And Weekday

Apr 5, 2011

How to get a date using Year (say 2009), Week (35) and Weekday (5). I have read only permission to database, so I can not create a function.

View 8 Replies View Related

SQL & PL/SQL :: Financial Year Wise Grouping?

Feb 20, 2012

I need to query by fiscal year wise.

For example

1-Apr-2010 to 31-Mar-2011

View 11 Replies View Related

SQL & PL/SQL :: Year To Date Value In Amount Field

Oct 18, 2010

I have a table and below are the rows:

create table employee_rev(employee_id number, month_name varchar2(10), month_end date, financial_year date, revenue_amt number)

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.

View 7 Replies View Related

SQL & PL/SQL :: De-Aggregate Year To Date Figures?

Apr 27, 2011

I have a table as below:

Year Month Value
2011 01 15
2011 02 26
2011 03 34

[code]...

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

[code]...

View 9 Replies View Related

SQL & PL/SQL :: Get First Week Starting With First SUNDAY Of Year

Nov 9, 2012

I want to get the week of the year.

Conditions are:

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

View 6 Replies View Related

SQL & PL/SQL :: Compare The Sales With The Same Month Last Year

Jul 6, 2012

I wanted to compare the sales with the same month last year. But the issue is only one value on each customers.

File Attached Table as JPG file..
-----------------------------------------------------------------------
SELECT WORDERS.BP_ORDER,
(SELECT SUM (WINVITEMS.ITEM_VAL)
FROM WINVITEMS
INNER JOIN WORDERS ON WORDERS.ORD_NO = WINVITEMS.ORD_NO
WHERE WORDERS.DATE_ORDER >= TRUNC(ADD_MONTHS(SYSDATE, -3),'MM')

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: Finding List Of Second And Forth Saturday In Particular Year?

Jun 30, 2011

i have one query. i want to Find the list of second and forth Saturday in a particular year.

View 6 Replies View Related

SQL & PL/SQL :: Parameter For Date - Quarter Year

Jan 26, 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

what i have to do for this

View 3 Replies View Related

PL/SQL :: GL_Cancelled_Date Displaying Incorrect Year?

Sep 24, 2013

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.

View 2 Replies View Related

SQL & PL/SQL :: Returning Old Value During Update?

Jan 2, 2012

In a pl/sql procedure, when I am doing an update, I need the old value to be returned and stored in a local variable, so that the same can be used for future purpose.

Note : I know the "OLD:" option is present when we use TRIGGER, but in my case , the table I am updating is a old table and I am not permitted to create a trigger for it.

View 9 Replies View Related

SQL & PL/SQL :: Returning Row With Same Values

Mar 22, 2012

create table testing
( id number (10),
key number (10)
)
insert into testing values (1,10)
insert into testing values (1,10)
insert into testing values (2,10)
insert into testing values (2,20)
insert into testing values (3,10)

My requirement is to return

id key
1 10
1 10

because both their id and key are same

View 2 Replies View Related

PL/SQL :: Returning Same Value Set In A Group

Jan 24, 2013

I have the below requirement,

i got a table(deptid, deptname, address,city,zip, state, other columns) i want to write a query to determine any error(records with different values) because i expect all records grouped-by(deptid, deptname, address,city,zip, state) to have the same deptid(pls note that deptid isnt unique),

or a specific deptid should have only one record based on the grouping (deptid, deptname, address,city,zip, state),

View 2 Replies View Related

PL/SQL :: Returning A Single Row

Jun 5, 2013

I have a table which lists previous jobs, whcih has monthto,yearto, monthfrom and year from fields. I am trying to find the most recent job (of which they might be multiple ending on the same month) Initially, i did the following:

ran a sub query to find the most recent (employee id, max (yearto * 12 + monthto), then another query where I got a list of the most recent jobs, and returned the first one using rownum = 1 (note this may be inconsistent between runs as thee rownum is calculcated before nay ordering clause. This worked, but i figured using the first function might be better as it gives an order

select employee_id from
(select employee_id
,max(NVL(yearto,0) *12 + NVL(monthto,1)) KEEP (DENSE_RANK FIRST ORDER BY emloyer) as latest
from employees
group by employee_id;

but this seems to return more than one entrty:

table date below:
drop table employee_list;
drop table employee_historic;
[code]...

What is the best way to return a single row, if the primary key is not available / applicable.

View 9 Replies View Related







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