Processing of numbers by Oracletrunc () and round () Functions

Processing of numbers by Oracletrunc () and round () Functions

Processing numbers by Oracle trunc () and round () Functions

1. TRUNC Function

1. TRUNC (for dates)

The date value intercepted by the TRUNC function for the specified element.
The syntax format is as follows:
TRUNC (date [, fmt])
Where:
Date: A date value.
Fmt date format, which is truncated by the specified Element format. Ignore it and it is intercepted by the latest date.
The usage of this function is as follows:
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 08:00:00 pm ', 'dd-mon-yyyy hh: mi am', 'hh') = '24-Nov-1999 am'

When no format is specified for round (date, ''format''), if the time in the date is before noon, 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. TRUNC (for number)
The 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 or select the part before or after the specified decimal number.
The syntax format is as follows:
TRUNC (number [, decimals])
Where:
Number the value to be intercepted
Decimals indicates the number of digits after the decimal point to be retained. Optional. If this parameter is ignored, all decimal parts are truncated.
The usage of this function is as follows:
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 format is set to 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 in a year, you need to use trunc (date, ''dd '').

To put it bluntly, the format is year, accurate to ----- year.

For months, accurate to ------ year, month (no matter which year, as long as it is the same month and day)
Is the day, accurate to ------ year, month, day (regardless of which month, only care about the day)

}

//////////////////////////////////////// ///////////////////////////////////////

Instance:

For numbers and dates
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-03
SQL> select trunc (234.5565) from dual;
TRUNC (234.5565)
---------------
234
SQL> select trunc (sysdate, 'D') from dual;
TRUNC (SYSD
----------
05-1-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 '));

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

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, 2nd days ');
Dbms_output.put_line (to_char (trunc (sysdate) + (1 + 9/24), 'yyyy-mm-dd hh24: mi: ss '));

End;
/
//////////////////////////////////////// ////////////////////////////////////

Common Oracle date functions (SYSDATE, Date Format)

SYSDATE
-- ◎ Obtain the current system time

Ex.
Select sysdate from dual;

Sysdate
----------
20-SEP-07

Common date formats

Date Format description
------------------------------------------------------------------------
YYYY/MM/DD -- year/month/day
YYYY-year (4 digits)
YYY -- year (3 digits)
YY-year (2 digits)
MM -- month
DD -- Date
D -- week
-- Sunday = 1 Monday = 2 Tuesday = 3
-- Wednesday = 4 Thursday = 5 Friday = 6 Saturday = 7

DDD-day of the year
WW -- the second day of the year
W -- December 1st, December 1st
YYYY/MM/DD HH24: MI: SS -- year/month/day (24 hours): minute: Second
YYYY/MM/dd hh: MI: SS -- year/month/day (not in 24 hours): minute: Second
J -- Julian day, Bc 4712/01/01 is 1
RR/MM/DD-2000 question
-- 00-49 = lower world region; 50-99 = lower world region
Ex.
Select to_char (sysdate, 'yyyy/MM/dd') from dual; -- 2007/09/20
Select to_char (sysdate, 'yyyy') from dual; -- 2007
Select to_char (sysdate, 'yyy') from dual; -- 007
Select to_char (sysdate, 'yy') from dual; -- 07
Select to_char (sysdate, 'mm') from dual; -- 09
Select to_char (sysdate, 'dd') from dual; -- 20
Select to_char (sysdate, 'D') from dual; -- 5
Select to_char (sysdate, 'ddd ') from dual; -- 263
Select to_char (sysdate, 'ww ') from dual; -- 38
Select to_char (sysdate, 'w') from dual; -- 3
Select to_char (sysdate, 'yyyy/MM/DD HH24: MI: ss') from dual; -- 15:24:13
Select to_char (sysdate, 'yyyy/MM/dd hh: MI: ss') from dual; -- 03:25:23
Select to_char (sysdate, 'J') from dual; -- 2454364

Select to_char (sysdate, 'rr/MM/dd') from dual; -- 07/09/20

Ii. ROUND Functions

Select round (number, [decimal_places]) FROM DUAL

Note:

Number: the value to be processed

Decimal_places: returns the number of decimal places. If not specified, the default value is 0.

Sample:

Select round (123.456) from dual; Result: 123

Select round (123.456, 0) from dual; Result: 123

Select round (123.456, 1) from dual; Result: 123.5

Select round (123.456, 2) from dual; Result: 123.46

Select round (123.456, 3) from dual; Result: 123.456

Select round (-123.456, 2) from dual; Result:-123.46