To find Month or year of a date

[gd_1976]

HI,
Is there any built in SQL or PL/SQL functions to find out a month or year of a date?.

Thanks.
Giri.

[raaj]

yeah there are.
can try out to_char function.
The function TO_CHAR enables a date to be converted on output to another format. The definition of the function is as follows:

TO_CHAR(date_col,'new_layout_description')

where 'new_layout_description' is a literal made up of date format components, some of which are:

MM month expressed as a number
MON mon as a three letter abreviation
MONTH padded as a 9 character length field
YYYY full year number
Y, YY or YYY last 1, 2 or 3 digits of year
YEAR year in words
DDD, DD or D day of year, month or week
DAY name of day padded to length 9 chars
HH or HH12 hour (12 hour clock)
HH24 hour (24 hour clock)
MI minute
SS seconds
fm used as a prefix to day and month and limits field to length
of word
th suffix on number giving th or st as appropriate (eg: 1st)
sp suffix indicating number is to be spetted out (eg: fourth)
Examples:
TO_CHAR(HIREDATE,'day ddth month year') gives
wednesday 17th december nineteen-eighty-one
TO_CHAR(HIREDATE,'DDD-DD/MM/YY' gives Wed-17/12/81

TO_CHAR(HIREDATE,'ddsp mon yyyy') gives seventeen dec 1981

TO_CHAR(HIREDATE,'mon') gives dec.

TO_CHAR(HIREDATE,'yyyy') gives 1981.

SELECT TO_CHAR(SYSDATE,'Day ddth fmMonth YYYY - HH:MI:SS') FROM DUAL; Thursday 25th Octiber 1990 - 8:56:49 Note that 'DUAL' is a special table available to all users from which the date, day and time may be extracted. The column holding this information is SYSDATE.

[gd_1976]

HI Raaj,
Thanks. I know about to_char. but when you use it in the Query which fetches more than 10,000 rows. it sucks. So, that's why i want to know whether Oracle has any built in functions for the same.
Thnx.
Giri.