Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Date Timestamp Functions

 

TRUNC() parameter list

Aspect Parameter Description Example CenturyCCTwo-digit century. 21CenturySCC Two-digit century with a negative sign (C) for B.C.-10Quarter QOne-digit quarter of the year.1 YearYYYYAll four digits of the year. 2006YearIYYY All four digits of the ISO year.2006Year RRRRAll four digits of the rounded year, which depends on the current year.2006 YearSYYYYAll four digits of the year with a negative sign (C) for B.C. C1001YearY,YYY All four digits of the year with a comma.2,006Year YYYLast three digits of the year.006 YearIYYLast three digits of the ISO year. 006YearYY Last two digits of the year.06Year IYLast two digits of the ISO year. 06Year RRLast two digits of the rounded year, which depends on the current year.06 YearYLast digit of the year. 6YearI Last digit of the ISO year.6Year YEARName of the year in uppercase.TWO THOUSAND-SIX YearYearName of the year with the first letter in uppercase. Two Thousand-SixMonthMM Two-digit month of the year.01Month MONTHFull name of the month in uppercase, right-padded with spaces to a total length of nine characters.JANUARY MonthMonthFull name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters. JanuaryMonthMON First three letters of the name of the month in uppercase.JANMonth MonFirst three letters of the name of the month with the first letter in uppercase.Jan MonthRMRoman numeral month. The Roman numeral month for the fourth month (April) is IV.WeekWW Two-digit week of the year.02Week IWTwo-digit ISO week of the year.02 WeekWOne-digit week of the month. 2DayDDD Three-digit day of the year.103Day DDTwo-digit day of the month.31 DayDOne-digit day of the week. 5DayDAY Full name of the day in uppercase.SATURDAYDay DayFull name of the day with the first letter in uppercase.Saturday DayDYFirst three letters of the name of the day in uppercase. SATDayDy First three letters of the name of the day with the first letter in uppercase.SatDay JJulian day-the number of days that have passed since January 1, 4713 B.C.2439892 HourHH24Two-digit hour in 24-hour format. 23HourHH Two-digit hour in 12-hour format.11Minute MITwo-digit minute.57 SecondSSTwo-digit second. 45SecondFF[1..9] Fractional seconds with an optional number of digits to the right of the decimal point. Only applies timestamps,When dealing with 0.123456789 seconds, FF3 would round to 0.123.SecondSSSSS Number of seconds past 12 a.m.46748Second MSMillisecond (millionths of a second).100 SecondCSCentisecond (hundredths of a second). 10Separators-/,.;: "text" Characters that allow you to separate the aspects of a date and time. You can supply freeform text in quotes as a separator.When dealing with the date December 13, 1969, DD-MM-YYYY would produce 12-13-1969 and DD/MM/YYYY would produce 12/13/1969Suffixes AM or PMAM or PM as appropriate.AM SuffixesA.M. or P.M.A.M. or P.M. as appropriate. P.M.SuffixesAD or BC AD or BC as appropriate.ADSuffixes A.D. or B.C.A.D. or B.C. as appropriate.B.C. SuffixesTHSuffix to a number. You can make the suffix uppercase by specifying the numeric format in uppercase and vice versa for lowercase.When dealing with a day number of 28, ddTH would produce 28th and DDTH would produce 28TH SuffixesSPNumber is spelled out.When dealing with a day number of 28, DDSP would produce TWENTY-EIGHT and ddSP would produce twenty-eight SuffixesSPTHCombination of TH and SP.When dealing with a day number of 28, DDSPTH would produce TWENTY-EIGHTH and ddSPTH would produce twenty-eighth EraEEFull era name for Japanese Imperial, ROC Official, and Thai Buddha calendars. No exampleEraE Abbreviated era name.No exampleTime zones TZHTime zone hour.12 Time zonesTZMTime zone minute. 30Time zonesTZR Time zone region.PSTTime zones TZDTime zone with daylight savings information.No example Quote from: Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback) # Paperback: 608 pages # Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004) # Language: English # ISBN-10: 0072229810 # ISBN-13: 978-0072229813 SQL> SQL> select sysdate   2  ,      trunc(sysdate,'CC')     as trunc_cc   3  from   dual; SYSDATE   TRUNC_CC --------- --------- 24-JUL-08 01-JAN-01 SQL>