Use the Oracle trunc to specify the exact month-day-time seconds ____oracle

This article is reproduced from: http://www.blogjava.net/gf7/archive/2012/07/01/381933.html

function trunc is an Oracle-built function that can be "tailored" to date type data to accommodate different types of data requirements.

In the previous issue of Oracle Date type operations, we have learned the basics of date types. A date type is a data type that includes the year, month, day, time, minute, and second, and can represent relatively accurate time information. On internal storage, the date type is similar to a numeric type and can be pushed and backed up by the addition and subtraction operation.

However, the precision of the date format often brings us some trouble, especially the time of the time seconds information. Most of the time, we don't need this part of the information. For example, specify date query, display only day information, and so on. Although some of this need can be achieved with TO_CHAR, it is more complex in format and has a data type conversion. Whether there is a method for changing data types, processing date-type data. The answer is the trunc function.

Trunc (date)

The function of trunc is to truncate the date type data in the specified format and return a date variable data. For example:

Sql> Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

To_char (sysdate, ' YYYY-MM-DDHH2

------------------------------

2010-12-10 20:39:58

Sql> Select Trunc (sysdate) from dual;

TRUNC (Sysdate)

--------------

2010-12-10

Sql> Select To_char (trunc (sysdate), ' Yyyy-mm-dd Hh24:mi:ss ') from dual;

To_char (TRUNC (sysdate), ' yyyy-m

------------------------------

2010-12-10 00:00:00

By default, the date type data returned by the Sysdate function includes the current specific time. Through trunc (date) processing, the direct interception to the day information, returns the specified day zero.

The trunc function also supports an overloaded parameter, the format string: trunc (date, ' format '), which specifies the location of the truncation. As follows:

truncated to Year

Sql> Select Trunc (sysdate, ' yyyy ') from dual;

TRUNC (sysdate, ' YYYY ')

---------------------

2010-1-1

truncated to Month

Sql> Select Trunc (sysdate, ' mm ') from dual;

TRUNC (sysdate, ' MM ')

-------------------

2010-12-1

Truncate to day

Sql> Select Trunc (sysdate, ' DD ') from dual;

TRUNC (sysdate, ' DD ')

-------------------

2010-12-10

Truncate to Hour

Sql> Select Trunc (sysdate, ' hh24 ') from dual;

TRUNC (sysdate, ' HH24 ')

---------------------

2010-12-10 20:00:00

Truncate to Minute

Sql> Select Trunc (sysdate, ' mi ') from dual;

TRUNC (sysdate, ' MI ')

-------------------

2010-12-10 20:52:00

Using different format flags, you can indicate different truncation locations for various 0 of times.

	format string	Description
Years	YYYY or Year	First day of the year (January 1 0 o'clock)
Month	MM or month	First day of the month (0 o'clock)
Day	DD or day	Date 0 o'clock (00:00:00)
Hours	HH or Hh24.	When is the whole (xx:00:00)
Part	Mi	A few minutes (xx:xx:00)

With the trunc function and date type addition and subtraction processing, we can implement some special date settings, implement Date function, use in such as job scheduling.

Tomorrow at 0.

Sql> Select To_char (trunc (sysdate) +1, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

To_char (TRUNC (sysdate) +1, ' YYYY

------------------------------

2010-12-11 00:00:00

10 O ' Day in the morning.

Sql> Select To_char (trunc (sysdate) +10/24, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

To_char (TRUNC (sysdate) +10/24, '

------------------------------

2010-12-10 10:00:00

Month third, 10:30 A.M.

Sql> Select To_char (trunc (sysdate, ' mm ') +2+10/24+1/48, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

Note: trunc (sysdate, ' mm ') will take the time to the current month of 1th Zero 0 minutes 0 seconds, then plus 2 means that the current month's 3 good zero 0 minutes 0 seconds, plus the ' 10/24 ' (' 10/24 ' itself means 10 hours) is to represent the month 3rd 10 minutes 0 seconds, plus ' 1/ 48 ' (' 1/48 ' itself means 30 minutes) means 3rd 10.3 minutes 0 seconds of the month

To_char (TRUNC (sysdate, ' MM ') +2+

------------------------------

2010-12-03 10:30:00

The more complex to_char,trunc can give play to the characteristics and advantages of the date type numeric value itself, whether the code neatness or the processing efficiency are worthy of attention.

To be extra, trunc itself also has the ability to handle digital truncation, which truncates the numeric type of a specified number of digits.

The default is truncated to integers, not rounded;

Sql> Select Trunc (15.743) from dual;

TRUNC (15.743)

-------------

15

Truncate to one digit after the decimal point;

Sql> Select Trunc (15.793,1) from dual;

TRUNC (15.793,1)

---------------

15.7

Truncate to the first decimal point;

Sql> Select Trunc (15.793,-1) from dual;

TRUNC (15.793,-1)

----------------

10

Trunc deals with numbers and date types, and also reflects the indirect relationship between date type data and numbers.

Original address: http://space.itpub.net/17203031/viewspace-681548

Trunc can be used not only to process dates, but also to process numbers

TRUNC (I1,I2) intercept the i1 i2 bit without rounding, if the i2 is on the right to intercept the decimal point, if the i2 is negative is to intercept the decimal point of the first few.

For example:

Select TRUNC (0.10005767,1) from dual; TRUNC (0.10005767,1) 1 0.1

And if the trunc function does not specify a parameter I2, the function is rounded and the rounding is not rounding

For example:

Select TRUNC (2.60005767) from dual; TRUNC (2.60005767)

The difference between the round function and the trunc function:

ROUND (I1,I2) rounded, i1 rounded, if i2 is to retain the decimal point after the i2 bit. If I2 is a negative number, it means to reserve the front of the decimal point (left ordinal)

TRUNC (I1,I2) intercept the i1 i2 bit without rounding, if the i2 is on the right to intercept the decimal point, if the i2 is negative is to intercept the decimal point of the first few.