tailieunhanh - Teach Yourself PL/SQL in 21 Days- P5

Teach Yourself PL/SQL in 21 Days- P5: Welcome to Sams Teach Yourself PL/SQL in 21 Days, Second Edition. The fact that you purchased this book indicates that you have an interest in learning the PL/SQL language, which is Oracle’s relational database procedural programming language. It allows you to develop powerful and complex programs to access and manipulate data in the Oracle8i database. We have attempted to include as many examples of PL/SQL code as possible to illustrate PL/SQL features | Using Oracle s Built-In Functions 177 Table Masks Used with the ROUND and TRUNC Functions Mask Options Description CC SCC Rounds or truncates to the century YYYY SYYYY YEAR SYEAR YYY YY Y Truncates to the year or rounds up to the next year after July 1st IYYY IYY IY I ISO year Q Truncates to the quarter or rounds up to the nearest quarter on or after the 16th day of the second month of the quarter MM MON MONTH RM Truncates the month or rounds up to the next month on or after the 16 th day DD DDD J Truncates or rounds to the day WW Same day of the week as the first day of the year IW Same day of the week as the first day of the ISO year W Same day of the week as the first day of the month Day Dy D Truncates or rounds to the first day of the week HH24 HH12 HH Truncates to the hour or rounds up to the next hour on or after 30 minutes MI Truncates to the minute or rounds up on or after 30 seconds Now that you have seen all the possible masking options try the TRUNC function by testing it with different examples. You will first truncate the time from the system date. Remember you still see the time displayed but if you use TRUNC on all dates the time is always 12 00 AM. instead of the time the date was assigned therefore all dates can be calculated properly regardless of time. Go ahead and execute the SQL code in Listing . Input Listing Removing the Time from SYSDATE 1 SELECT TO_CHAR TRUNC SYSDATE MM DD YYYY HH MM SS AM1 2 Today s Date and Time 3 from DUAL 6 Your output appears similar to Output Today s Date and Time 06 21 1999 12 00 00 AM Analysis Notice that the time element is still displayed but if you were to subtract two truncated dates with the same time you get an even number of days. One more observation is that the default for TRUNC is the same as a format mask of DD which simply eliminates the need to worry about the time in your calculations. 178 Day 6 You can test the TRUNC function by truncating the SYSDATE to the nearest quarter by executing