first day of the year

NiceMan331
NiceMan331 used Ask the Experts™
on
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;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Most Valuable Expert 2011
Top Expert 2012
Commented:
assuming :basic_block.c_per is a text field try this

SELECT trunc(to_date(:BASIC_BLOCK.c_per ,'yyyy-mm-dd'),'yyyy') into :begd  FROM Dual;

or

:begd := trunc(to_date(:BASIC_BLOCK.c_per ,'yyyy-mm-dd'),'yyyy');


if :basic_block.c_per is a date then you can simply do...


SELECT trunc(:BASIC_BLOCK.c_per) into :begd  FROM Dual;

or

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

Author

Commented:
:basic_block.c_per is a date field and formatted as 'yyyy-mm-dd'
that one doesnt work also
sdstuber
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> :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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
NiceMan331

Author

Commented:
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
sdstuber
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Author

Commented:
So , what would be the formula looks like ?
sdstuber
Most Valuable Expert 2011
Top Expert 2012

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


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

Author

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial