asked on

first day of the year

hi
in oracle form , how i can return first day of the year for a given date
field name is : :BASIC_BLOCK.c_per
i found this function
SELECT TRUNC(to_date('17-DEC-2001'),'YEAR') "First Day" FROM Dual;
but it doesnt work when i replaced it like this
declare
begd date;
begin
SELECT to_date(:BASIC_BLOCK.c_per ,'Yyyy-mm-dd') into begd FROM Dual;

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NiceMan331

ASKER

:basic_block.c_per is a date field and formatted as 'yyyy-mm-dd'
that one doesnt work also

Sean Stuber

>>> :basic_block.c_per is a date field and formatted as 'yyyy-mm-dd'

no

a date does not have a format
text has a format.

>>> that one doesnt work also

I gave 4 options, which "one" ?

and "doesn't work" in what way?

NiceMan331

ASKER

i didnt see your update for the comment
this one now works
begd := trunc(:BASIC_BLOCK.c_per,'yyyy');

but in the query , it required to be formatted as : yyyy-mm-dd
the form doesnt accept it

Sean Stuber

in the form you are typing in text, that text must have a format, and then the form converts the text by that format into a date.

NiceMan331

ASKER

So , what would be the formula looks like ?

Sean Stuber

You have it already, since :basic_block.c_per is a date

begd := trunc(:BASIC_BLOCK.c_per,'yyyy');

NiceMan331

ASKER

begd := trunc(:BASIC_BLOCK.c_per,'yyyy');
doesnt work , it sent me error < ORA-00932
this one only works :

 to_date(''' || to_char(trunc(:BASIC_BLOCK.c_per,'yyyy'),'YYYY-MM-DD') || ''',''YYYY-MM-DD'')

Open in new window

i understand from you that date field doesn't required format , but it really doesn't work
, in sql it works , but in form no

NiceMan331

ASKER

i accepted the solution as it works in sql-plus only