To find Month or year of a date
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: To find Month or year of a date

  1. #1
    Join Date
    Jun 2001
    Location
    NJ
    Posts
    118

    Question

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

    Thanks.
    Giri.

  2. #2
    Join Date
    May 2001
    Location
    singapore
    Posts
    19
    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.

    thanx with regards,
    Raj

  3. #3
    Join Date
    Jun 2001
    Location
    NJ
    Posts
    118
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width