A study of two time functions of Oracle

Recently, we often use time functions, write a note here, record your income, and hope to help you.

1, for a time such as Sysdate:2015/1/30 14:16:03 how to get only the month and day, at the same time its data type does not change it?

The easiest to think of is to_char and then in To_date, before I can think of this, because I feel trouble in the Internet search, found another way, trunc (sysdate), can get the desired results

Select Sysdate from dual; Sysdate-----------2015/1/30 1  Select To_date (To_char (sysdate, ' yyyymmdd '), ' YYYYMMDD ') from dual; To_date (To_char (sysdate, ' Yyyym------------------------------2015/1/30  Select Trunc (sysdate) from dual; TRUNC (sysdate)--------------2015/1/30

2, with the above bedding in the following time than the right is much easier

There is a need for me to revise a piece of data on the first day of each quarter, which must have been added 10 days prior to the previous quarter. That is: July 1 Modify the data added before April 10 (including)

This is the time to use the Months_between function, at the beginning of the period I think so,

If the data is added time is April 10 23:59 59 seconds, so and July 1 The result of the operation is: 2.67741972

If the data is added time is April 11 0:0 0 seconds, so and July 1 The result of the operation is: 2.67741935

The results of the April 10 and July 10 calculations are: 2.70967741

In order to ensure the controllability of time, think of the use of the day, to remove the time of cent seconds

The specific code is as follows: (using To_date (' 20150701 ', ' YYYYMMDD ') to simulate time parameters)

Select Months_between (to_date (' 20150701 ', ' YYYYMMDD '), To_date (' 20150410 23:59:59 ', ' yyyymmdd HH24:mi:ss ')) time from Dual;       Time----------2.67741972  Select Months_between (to_date (' 20150701 ', ' YYYYMMDD '), To_date (' 20150411 00:00:00 ', ' YYYYMMDD HH24:mi:ss ') time from dual;       Time----------2.67741935  Select Months_between (to_date (' 20150701 ', ' YYYYMMDD '), to_date (' 20150410 ', ' YYYYMMDD ') )) time from dual;       Time----------2.70967741 Select Months_between (to_date (' 20150701 ', ' YYYYMMDD '), Trunc (To_date (' 20150410 15:23:38 ', ' YYYYMMDD HH24:mi:ss ')) time from dual;       

Ultimately, it is not necessary to use the combination of trunc and months_between to accomplish this requirement.

A study of two time functions of Oracle