formats used with the TRUNC() function for the DATE data type : TRUNC Date « Date Timezone « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Date Timezone
  3. TRUNC Date

formats used with the TRUNC() function for the DATE data type

   

Format Element    Description                                                         Example

CC                One greater than the first two digits of a four-digit year.         ROUND(SYSDATE,'CC')

SCC               One greater than the first two digits of a four-digit year.         ROUND(SYSDATE,'SCC')

Year              

SYYYY             Year; rounds up on July 1.                                          ROUND(SYSDATE,'SYYYY')

YYYY              Year; rounds up on July 1.                                          ROUND(SYSDATE,'YYYY')

YEAR              Year; rounds up on July 1.                                          ROUND(SYSDATE,'YEAR')

YYY               Year; rounds up on July 1.                                          ROUND(SYSDATE,'YYY')

YY                Year; rounds up on July 1.                                          ROUND(SYSDATE,'YY')

Y                 Year; rounds up on July 1.                                          ROUND(SYSDATE,'Y')

IYYY              ISO year.                                                           ROUND(SYSDATE,'IYYY')

IY                ISO year.                                                           ROUND(SYSDATE,'IY')

I                 ISO year.                                                           ROUND(SYSDATE,'I')

Q                 Quarter; rounds up on sixteenth day of the second month of the quarter. ROUND(SYSDATE,'Q')

Month
       

MONTH             Month; rounds up on sixteenth day.                                 TRUNC(SYSDATE,'MONTH')

MON               Month; rounds up on sixteenth day.                                 TRUNC(SYSDATE,'MON')

MM                Month; rounds up on sixteenth day.                                 TRUNC(SYSDATE,'MM')

RM                Month; rounds up on sixteenth day.                                 TRUNC(SYSDATE,'RM')

Week
       

WW                Same day of the week as the first day of the year.                 TRUNC(SYSDATE,'WW')

IW                Same day of the week as the first day of the ISO year.             TRUNC(SYSDATE,'IW')

W                 Same day of the week as the first day of the month.                TRUNC(SYSDATE,'W')

Day
       

DD                Day of the month (from 1 to 31).                                   ROUND(SYSDATE,'DD')

DDD               Day of the year (from 1 to 366).                                   ROUND(SYSDATE,'DDD')

J                 Day.                                                               ROUND(SYSDATE,'J')


Starting Day of the week.                                          

DY                Starting Day of the week.                                         TRUNC(SYSDATE,'DY')

DAY               Starting Day of the week.                                         TRUNC (SYSDATE,'DAY')

HH                Hour of the day (from 1 to 12).                                   TRUNC (SYSDATE,'HH')

HH12              Hour of the day (from 1 to 12).                                   TRUNC (SYSDATE,'HH12')

HH24              Hour of the day (from 0 to 23).                                   TRUNC (SYSDATE,'HH24')

MI                Minute (from 0 to 59).                                            TRUNC (SYSDATE,'MI') 

--








Related examples in the same category

1.Trunc a date
2.Truncates 7:45:26 P.M. on May 25, 2005, to the hour
3.TRUNC(SYSDATE,'Q'): Truncating to the quarter
4.TRUNC: Removing the time from SYSDATE
5.Use TRUNC(Date value) in where clause
6.truncate and round a date that falls into the second part of a year, the results would be different:
7.Use TRUNC(date_variable) when you want to extract a date from a timestamp
8.Truncates May 25, 2005, to the first day in the month
9.TRUNC(): truncate May 25, 2005, to the first day in the year