Link to home
Start Free TrialLog in
Avatar of NiceMan331

asked on

first day of the year

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
begd date;
SELECT to_date(:BASIC_BLOCK.c_per ,'Yyyy-mm-dd') into begd  FROM Dual;
Avatar of Sean Stuber
Sean Stuber

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NiceMan331


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


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?
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
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.
So , what would be the formula looks like ?
You have it already, since :basic_block.c_per is a date

begd := trunc(:BASIC_BLOCK.c_per,'yyyy');
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
i accepted the solution as it works in sql-plus only