Retriving Year Number Of Data Parameter

try:

to_date( :p1 , 'YYYY' )

what is :p1   ?  if it's a date
what is year?  is it a number?

assuming date and number then these should work (basically same thing you started with)


select op_bal from acc_bal
where acc_no=:p3
and year =  to_number(to_char(:p1,'yyyy'))

or

select op_bal from acc_bal
where acc_no=:p3
and year =  extract(year from :p1)


no need to use the subquery from dual


by "not accept"  - what happens?

error? no results? wrong result? key board catches f ire?

sorry

select trunc(to_date( :p1 , 'yyyy-mm-dd'),'yyyy')
from dual

>>>> trunc(to_date( :p1 , 'yyyy-mm-dd'),'yyyy')

that assumes :p1 is a string in the form of yyyy-mm-dd,  if that's true that's a heck of a guess, I don't see anything above that indicates either the data type or the format of the content if it is a string.

yes sdstuber you are right
p1 is a date , and not necessary to be starting of the year
i'll try your code tomorrow
the error i got is regarding : from
it said bad bind from cluuase

your code? I don't see much difference in code :)

As I noted above, I didn't really change anything significantly from what you started with.,
I was simply clarifying that what you had should have worked, but cleaned it up a little.

how/where are you using your queries?  Are they inside a pl/sql procedure or function?
If so, you don't need to use colon in front of the variables, simply reference the name directly.

yes , i use them within a function as program unit of a report

Is this what you're getting?

SQL> select count(*) from user_tables where last_analyzed > :p1;
SP2-0552: Bind variable "P1" not declared.
SQL>


if so, you need to declare the variable first,  however you can't declare a date bind variable in the sql*plus context

SQL> variable p1 date
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

you'll instead have to declare a string and populate it,  next example shows how to do that using the format from PortletPaul's guess above

SQL> variable p1 varchar2(10)
SQL> exec :p1 := '2013-05-31';

PL/SQL procedure successfully completed.

now though, because the string requires conversion to a date to be useful, if you don't you'll get errors or unreliable results

SQL> select count(*) from user_tables where last_analyzed > :p1;
select count(*) from user_tables where last_analyzed > :p1
                                                        *
ERROR at line 1:
ORA-01861: literal does not match format string


so we do an explicit conversion using PortletPaul's suggested format

SQL> select count(*) from user_tables where last_analyzed > to_date(:p1,'yyyy-mm-dd');

  COUNT(*)
----------
         0

oops, didn't see your last response
so, you're doing something like this...


SQL> create or replace function test_func(p1 in date) return integer
  2  is
  3      v_cnt integer;
  4  begin
  5      select count(*) into v_cnt from user_tables where last_analyzed > :p1;
  6  end;
  7  /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION TEST_FUNC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/71     PLS-00049: bad bind variable 'P1'



if so,  simply remove the colon


SQL> create or replace function test_func(p1 in date) return integer
  2  is
  3      v_cnt integer;
  4  begin
  5      select count(*) into v_cnt from user_tables where last_analyzed > p1;
  6  end;
  7  /

Function created.

actually the error were not related to :p1 , but related to keyword FROM where it used as subquery
anyhow , i'm out now of my desk , i can't cee the real error till tomorrow
so , if you please give me a chance for tomorror ,
i like your last function , but could you please asjust it to retrive year number of date
like this

create or replace function test_func(p1 in date) return integer
  2  is
  3      v_cnt integer;
  4  begin
      select  to_number(to_char(:p1,'yyyy'))  into v_cnt from dual ;
  5       
  6  end;

Select allOpen in new window

is will work ?

great
i think this is the best solution
i'll try it tomorrow

the function works
thanx