-3

I have year as a parameter in proc which can be 2017,2018,etc.

I want get 1 Jan of that year in Date Format in oracle server.

Is there any way to do that ?

Yogesh
  • 354
  • 2
  • 15
  • Where do you have the year stored ( a variable, a table, a parameter, ...)? And what do you need to do ( a simple query, a procedure, ...)? And what have you tried so far? – Aleksej Jan 23 '17 at 16:52
  • i have it as a parameter in proc – Yogesh Jan 23 '17 at 16:52
  • What type is the parameter ( number, varchar, ...) ? And what do you need to do: do you need to print the value? to use it in an OUT parameter? to insert into some table, ? Also, please post your code. – Aleksej Jan 23 '17 at 16:55
  • it is as number, i want to use that in where clause – Yogesh Jan 23 '17 at 16:56
  • https://i.imgur.com/WlYODkP.png – Sebas Jan 23 '17 at 16:58

4 Answers4

2

Assuming your parameter is P_year...

TO_DATE('01/01/'||P_year,'DD/MM/YYYY')
Christian Palmer
  • 1,262
  • 8
  • 10
2

You may need something like:

create or replace procedure checkDate ( pYear IN number) is
    vNum number;
begin
    select count(1)
    into vNum
    from ( select sysdate as dateColumn from dual) someTable 
    where dateColumn > to_date('01-01-' || pYear, 'dd-mm-yyyy');
    --
    dbms_output.put_line(vNum);
end;
Aleksej
  • 22,443
  • 5
  • 33
  • 38
1

Simply converting a year to a date will do the trick

SQL> select to_date('2017','YYYY') from dual;

TO_DATE('2017','YYY
-------------------
01/01/2017 00:00:00

but if you are concerned that at some stage this default might change, you can also truncate the data down to the year to be absolutely sure

SQL> select trunc(to_date('2017','YYYY'),'YYYY') from dual;

TRUNC(TO_DATE('2017
-------------------
01/01/2017 00:00:00
Connor McDonald
  • 10,418
  • 1
  • 11
  • 16
0

As metioned above TRUNC will be best suited in this case.

SET define ON;
SET serveroutput ON;
DECLARE
  lv_var  VARCHAR2(100):='&Enter_year';
  lv_date DATE;
begin
  SELECT TRUNC(to_date(lv_var,'YYYY')) INTO lv_date FROM dual;
  dbms_output.put_line(lv_date||' --> Req date');
END;
Avrajit Roy
  • 3,233
  • 1
  • 13
  • 25