Mega Code Archive

 

Categories / Oracle PLSQL / Date Timezone

 

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')  --