Analysis of Using TRUNC (for dates) in ORACLE

The date value intercepted by the 1.1.TRUNC (for dates) TRUNC function for the specified element. The specific syntax format is as follows: TRUNC (date [, fmt]) Where: date is a date value in the fmt date format, which will be truncated by the specified Element format. Ignoring it is truncated by the latest date. below is the usage of the function: TRUNC (TO_DATE ('24-Nov-1999 pm ', 'dd-mon-yyyy hh: mi am ') = '24-Nov-1999 12:00:00 am' TRUNC (TO_DATE ('24-Nov-1999 pm ', 'dd-mon-yyyy hh: mi am ', 'hh ') = '24-Nov-1999 08:00:00 am' round (date, ''format'') If format is not specified, if the time in the date is before noon, then, the time in the date is truncated to 12. m. (midnight, the beginning of a day), or enter the next day. If format is not specified for TRUNC (date, ''format''), the date is cut to 12 A. M., and the condition before noon is not considered. 2. the TRUNC (for number) TRUNC function returns the processed value. Its working mechanism is very similar to that of the ROUND function, except that the function does not ROUND the part before or after a specified decimal number, all of them are intercepted. The specific syntax format is as follows: TRUNC (number [, decimals]) Where: www.2cto.com number the number to be intercepted decimals specifies the number of digits after the decimal point to be retained. Optional. If it is ignored, it is truncated. The following figure shows the function usage: TRUNC (89.985, 2) = 89.98 TRUNC (89.985) = 89 TRUNC (89.985,-1) = 80 Note: The second parameter can be a negative number, indicating that the part after the specified number of digits on the left of the decimal point is truncated. {When the format is day, It is accurate to the day, regardless of the number of months in a few years as long as it is the correct day, to determine a day of the year, you need to use trunc (date, ''dd ''). to put it bluntly, when format is set to year, It is accurate to ----- year is month, accurate to ------ year, month (no matter which year, as long as it is the same month and day) is day, accurate to ------ year, month, and day (regardless of which month, only care about which day )} //////////////////////////////////////// /// //: SQL> select trunc (sysdate) from dual; TRUNC (SYSD ---------- month-03 SQL> select trunc (sysdate, 'mm') from dual; TRUNC (SYSD -------- -- 01-1 month-03 SQL> select trunc (sysdate, 'yy') from dual; TRUNC (SYSD -------- 01-1 month-03 SQL> select trunc (234.5565) from dual; TRUNC (234.5565) www.2cto.com --------------- 234 SQL> select trunc (sysdate, 'D') from dual; TRUNC (SYSD ---------- 05-03 //////////////////////////////// //////////////////////////////////////// /// select trunc (sysdate, 'dd') from dual; -- 2007-9-19 select trunc (sysdate, 'Yyyy') from dual; -- 2007-1-1 select trunc (sysdate, 'mm') from dual; -- 2007-9-1begin dbms_output.put_line (to_char (sysdate), 'yyyy-mm-dd hh24: mi: ss'); dbms_output.put_line (to_char (sysdate) + 1/24/60/10, 'yyyy-mm-dd hh24: mi: ss ')); dbms_output.put_line (to_char (sysdate) + 10/(24*60*60), 'yyyy-mm-dd hh24: mi: ss ')); dbms_output.put_line (to_char (trunc (sysdate) + 10/(2 4*60*60), 'yyyy-mm-dd hh24: mi: ss'); end; begin dbms_output.put_line ('current Time '); www.2cto.com dbms_output.put_line (to_char (sysdate), 'yyyy-mm-dd hh24: mi: ss'); dbms_output.put_line ('current time + 1 s '); dbms_output.put_line (to_char (sysdate) + (1/24)/60)/60), 'yyyy-mm-dd hh24: mi: ss ')); dbms_output.put_line ('current time + 1 s'); dbms_output.put_line (to_char (sysdate) + (5/24 )/ 60)/60), 'yyyy-mm-dd hh24: mi: ss'); dbms_output.put_line ('current time + 10 s'); dbms_output.put_line (to_char (sysdate) + (10/(24*60*60), 'yyyy-mm-dd hh24: mi: ss'); dbms_output.put_line ('current Day '); dbms_output.put_line (to_char (trunc (sysdate), 'yyyy-mm-dd hh24: mi: ss'); dbms_output.put_line ('current 2nd day '); dbms_output.put_line (to_char (trunc (sysdate) + (1 + 1/24), 'yyyy-m M-dd hh24: mi: ss'); dbms_output.put_line ('current on 2nd days'); dbms_output.put_line (to_char (trunc (sysdate) + (1 + 9/24 ), 'yyyy-mm-dd hh24: mi: ss'); www.2cto.com end; //////////////////////////////////////// /// // common oracle date function (SYSDATE, Date Format) 1 SYSDATE 2 -- ◎ the current system time is 3 4 ex. 5 select sysdate from dual; 6 7 sysdate 8 ---------- 9 20-SEP-07 10 11 commonly used Date Format 12 13 Format description: 14 ------------------------------------------------------------------------ 15 YYYY/MM/DD -- year/month/day 16 YYYY -- year (4 digits) 17 YYY -- year (3 digits) 18 YY-year (2 digits) 19 MM -- month 20 DD -- date 21 D -- week 22 -- Sunday = 1 Monday = 2 Tuesday = 3 23 -- wedday = 4 Thursday = 5 Friday = 6 Saturday = 7 24 25 DDD -- 26 WW of the seventh day of the year -- 27 W of the seventh day of the year -- 28 YYYY/MM/DD HH24 of the seventh day of the month: MI: SS -- YYYY/MM/dd hh: MI: SS-year/month/day (not in 24 hours): minute: Second 30 J -- Julian day, Bc 4712/01/01 is 1 31 RR/MM/DD -- AD 2000 question 32 -- 00-49 = lower generation; 50-99 = world famous 33 ex. 34 select to_char (sysdate, 'yyyy/MM/dd') from dual; -- 2007/09/20 35 select to_char (sysdate, 'yyyy') from dual; -- 2007 36 select to_char (sysdate, 'yyy') from dual; -- 007 37 select to_char (sysdate, 'yy') from dual; -- 07 38 select to_char (sysdate, 'mm') from dual; -- 09 39 select to_char (sysdate, 'dd') FRO M dual; -- 20 40 select to_char (sysdate, 'D') from dual; -- 5 41 select to_char (sysdate, 'ddd ') from dual; -- 263 42 select to_char (sysdate, 'ww ') from dual; -- 38 43 select to_char (sysdate, 'w') from dual; -- 3 44 select to_char (sysdate, 'yyyy/MM/DD HH24: MI: ss') from dual; -- 2007/09/20 15:24:13 45 select to_char (sysdate, 'yyyy/MM/dd hh: MI: ss ') from dual; -- 2007/09/20 03:25:23 46 select to_char (sysdat E, 'J') from dual; -- 2454364 47 select to_char (sysdate, 'rr/MM/dd') from dual; -- The date value intercepted by the TRUNC function 07/09/20 for the specified element. Www.2cto.com
The specific syntax format is as follows: TRUNC (date [, fmt]) Where: date is a date value in the fmt date format, which will be truncated by the specified Element format. Ignoring it is truncated by the latest date. below is the usage of the function: TRUNC (TO_DATE ('24-Nov-1999 pm ', 'dd-mon-yyyy hh: mi am ') = '24-Nov-1999 12:00:00 am' TRUNC (TO_DATE ('24-Nov-1999 pm ', 'dd-mon-yyyy hh: mi am ', 'hh ') = '24-Nov-1999 08:00:00 am' round (date, ''format'') If format is not specified, if the time in the date is before noon, then, the time in the date is truncated to 12. m. (midnight, the beginning of a day), or enter the next day. If format is not specified for TRUNC (date, ''format''), the date is cut to 12 A. M., and the condition before noon is not considered. 2. the TRUNC (for number) TRUNC function returns the processed value. Its working mechanism is very similar to that of the ROUND function, except that the function does not ROUND the part before or after a specified decimal number, all of them are intercepted. The specific syntax format is as follows: TRUNC (number [, decimals]) Where: number: the number to be truncated. decimals indicates the number of digits after the decimal point to be retained. Optional. If it is ignored, it is truncated. The following figure shows the function usage: TRUNC (89.985, 2) = 89.98 TRUNC (89.985) = 89 TRUNC (89.985,-1) = 80 Note: The second parameter can be a negative number, indicating that the part after the specified number of digits on the left of the decimal point is truncated. {When the format is day, It is accurate to the day, regardless of the number of months in a few years as long as it is the correct day, to determine a day of the year, you need to use trunc (date, ''dd ''). www.2cto.com stands for the format of year, accurate to ----- year is month, accurate to ------ year, month (no matter which year, as long as it is the same month and day) is day, accurate to ------ year, month, and day (regardless of which month, only care about which day )} //////////////////////////////////////// /// //: SQL> select trunc (sysdate) from dual; TRUNC (SYSD ---------- month-03 SQL> select trunc (sysdate, 'mm') from dual; TRUNC (SYSD ---------- 01-1-03 SQL> select trunc (sysdate, 'yy') from dual; TRUNC (SYSD -------- 01-1 month-03 SQL> select trunc (234.5565) from dual; TRUNC (234.5565) --------------- 234 SQL> select trunc (sysdate, 'D') from dual; TRUNC (SYSD ---------- 05-03 //////////////////////////////// //////////////////////////////////////// /// select trunc (sysdate, 'dd') from dual; -- 2007-9-19 select trunc (sysdate, 'yyyy') from dual; -- 2007-1-1 select trunc (sysdate, 'mm') from dual; -- 2007-9-1 begin dbms_output.put_line (to_char (sysdate), 'yyyy-mm-dd hh24: mi: ss'); dbms_output.put_line (to_char (sysdate) + 1/24/60/10, 'yyyy-mm-dd hh24: mi: ss'); dbms_output.put_line (to_char (sysdate) + 10/(24*60*60 )), 'yyyy-mm-dd hh24: mi: ss'); www.2cto.com dbms_output.put_line (to_char (trunc (sysdate) + 10/(24*60*60 )), 'yyyy-mm-dd hh24: mi: ss'); end; begin dbms_output.put_line ('current Time'); dbms_output.put_line (to_char (sysdate ), 'yyyy-mm-dd hh24: mi: ss'); dbms_output.put_line ('current time + 1 s'); dbms_output.put_line (to_char (sysdate) + (1/24) /60)/60), 'yyyy-mm-dd hh24: mi: ss'); dbms_output.put_line ('current time + 1 s'); dbms_output.put_line (to_char (sysdate) + (5/24)/60)/60), 'yyyy-mm-dd hh24: mi: ss'); dbms_output.put_line ('current time + 10 s '); dbms_output.put_line (to_char (sysdate) + (10/(24*60*60), 'yyyy-mm-dd hh24: mi: ss ')); www.2cto.com dbms_output.put_line ('current Day'); dbms_output.put_line (to_char (trunc (sysdate), 'yyyy-mm-dd hh24: mi: ss ')); dbms_output.put_line ('current 2nd days '); dbms_output.put_line (to_char (trunc (sysdate) + (1 + 1/24), 'yyyy-mm-dd hh24: mi: ss '); dbms_output.put_line ('current on 2nd Day'); dbms_output.put_line (to_char (trunc (sysdate) + (1 + 9/24), 'yyyy-mm-dd hh24: mi: ss'); end; //////////////////////////////////////// /// // common oracle date function (SYSDATE, Date Format) 1 SYSDATE 2 -- ◎ the current system time is 3 4 ex. 5 select sysdate from dual; 6 7 sysdate 8 ---------- 9 20-SEP-07 10 11 commonly used Date Format 12 13 Date Format description 14 Jun 15 YYYY/MM/DD -- year/month/day 16 YYYY -- year (4 digits) 17 YYY -- year (3 digits) 18 YY -- year (2 digits) 19 MM -- month 20 DD -- date 21 D -- week 22 -- Sunday = 1 Monday = 2 Tuesday = 3 23 -- wedday = 4 Thursday = 5 Friday = 6 Saturday = 7 24 25 DDD -- 26 WW of the seventh day of the year -- 27 W of the seventh day of the year -- 28 YYYY/MM/DD HH24 of the seventh day of the month: MI: SS -- YYYY/MM/dd hh: MI: SS-year/month/day (not in 24 hours): minute: 30 J -- Julian day, bc 4712/01/01 for 1 www.2cto.com 31 RR/MM/DD -- AD 2000 question 32 -- 00-49 = lower generation time; 50-99 = lower generation time 33 ex. 34 select to_char (sysdate, 'yyyy/MM/dd') from dual; -- 2007/09/20 35 select to_char (sysdate, 'yyyy') from dual; -- 2007 36 select to_char (sysdate, 'yyy') from dual; -- 007 37 select to_char (sysdate, 'yy') from dual; -- 07 38 select to_char (sysdate, 'mm') from dual; -- 09 39 select to_char (sysdate, 'dd') from dual; -- 20 40 select to_char (sysdate, 'D') from dual; -- 5 41 select to_char (sysdate, 'ddd ') from dual; -- 263 42 select to_char (sysdate, 'ww') from dual; -- 38 43 select to_char (sysdate, 'w') from dual; -- 3 44 select to_char (sysdate, 'yyyy/MM/DD HH24: MI: ss') from dual; -- 2007/09/20 15:24:13 45 select to_char (sysdate, 'yyyy/MM/dd hh: MI: ss') from dual; -- 2007/09/20 03:25:23 46 select to_char (sysdate, 'J') from dual; -- 2454364 47 select to_char (sysdate, 'rr/MM/dd') from dual; -- 07/09/20 author zhu473105308