Time function is often used recently , Write a note here , Record what you get , I hope it will help you too .

1、 For a time like sysdate：2015/1/30 14:16:03 How to just get the date , At the same time, its data type does not change ？

The easiest thing to think of is to_char And then in to_date, All I could think of before was this , Because I found it troublesome to search on the Internet , Found another way ,trunc(sysdate), You can get the result you want 

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 foreshadowing above, it's much easier to compare the time below

There is such a need , I'm going to modify one piece of data on the first day of each quarter , This data must have been added 10 days before the last quarter . namely ：7 month 1 Revised by day 4 month 10 Japan （ Include ） Previously added data

This is the time to use MONTHS_BETWEEN function , That's what I thought at the beginning ,

If the data was added at 4 month 10 Japan 23 spot 59 branch 59 second , And so 7 month 1 The result of daily operation is ：2.67741972

If the data was added at 4 month 11 Japan 0 spot 0 branch 0 second , And so 7 month 1 The result of daily operation is ：2.67741935

and 4 month 10 Day and 7 month 10 The result of the day is ：2.70967741

To make sure the time is controllable , Think of the date of use , Remove the hours, minutes and seconds of time

The specific code is as follows ：（ Use 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;
TIME

----------

2.70967741

In the end, you don't have to use trunc and months_between It's a combination of the two to fulfill this requirement .

oracle A few more articles about time function

  1. mysql And oracle Date / Summary of time function

    Preface The date of this article / Time is all formatted as ”2016-01-01 01:01:01“ form : MONITOR_TIME For database table fields : Strings and dates / Time to time transfer function Oracle date / Time to string function :to ...

  2. Oracle Time function usage in (to_date、to_char) ( summary ）

    One .24 It's shown in the form of hours HH24 select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual; select to_date('2005- ...

  3. Oracle Commonly used SQL Time function

    1. Query the current date and time select sysdate from dual; 2. Query the last day of the month select last_day(sysdate) from dual; 3. How many months before and after the inquiry ) fro ...

  4. oracle Date time function summary

    Often write sql My classmates should come into contact with some oracle The date time function of , such as : Financial software or human resource software must be in accordance with the annual , Quarterly , monthly , Even do statistics every week . I have nothing to do today , Specially from the Internet to sort out a ...

  5. Oracle Date time function

    ORACLE Date time function TO_DATE Format ( In time :2007-11-02 13:45:25 For example ) Year: yy two digits Two years Display value :07 yyy three digits ...

  6. ORACLE Time function (SYSDATE) In depth understanding of

    ORACLE Time function (SYSDATE) In depth understanding of Add  select sysdate,add_months(sysdate,12) from dual; -- Add 1 year  select sysdate,add ...

  7. oracle Time function

    Add  select sysdate,add_months(sysdate,12) from dual; -- Add 1 year  select sysdate,add_months(sysdate,1) from ...

  8. [ turn ]ORACLE Date time function

    In this paper, from :http://www.cnblogs.com/chuncn/archive/2009/04/29/1381282.html ORACLE Date time function TO_DATE Format ( In time : ::2 ...

  9. [ Reprint ]ORACLE Date time function

    ORACLE Date time function TO_DATE Format ( In time :2007-11-02   13:45:25 For example )           Year:              yy two digits Two years ...

Random recommendation

  1. The first 19 Chapter Collections framework (3)-Map Interface

    The first 19 Chapter Collections framework (3)-Map Interface 1.Map Interface Overview Map It's a mapping , So what is a mapping relationship ? The mathematical explanation of mapping set up A,B It's two non empty sets , If there is a law , Make right A Every element in a, According to the law f, stay ...

  2. git stuff

    git stuff trick git bash Unable to mark replication solution git bash Click the icon in the upper left corner of the window , Select Properties -> Options -> Quick Edit mode It's OK ok 了 Usual Commands gen ...

  3. JMeter The foundation -- The scope and execution order of the component

    It was introduced earlier jmeter Component category of , For starters ,jmeter There are still a lot of components , If we learn according to the meaning of each parameter of each component , It will undoubtedly reduce the enthusiasm of learning performance test , Even if we are familiar with all the components and their parameters ...

  4. solve ubuntu Lower installation mysql Use service Can't start problem

    When I started, I found that service mysql start Rather than invoking init scripts through /etc/init.d, use the service(8) ...

  5. log4net Detailed configuration description

    Original address :http://blog.sina.com.cn/s/blog_671486bc01011rdj.html 1. summary log4net yes .Net The next excellent open source logging component .log4net remember ...

  6. Merge Into example

    merge into users a using temp_users b on (a.userid = b.user_id) when matched then update set a.passw ...

  7. ant To get started Configuration and installation

    Need to use... Recently ant To generate files ,java class . I'm just beginning to understand this tool . Take a close look at , Feel the power of this gadget . Bloggers are also beginners , Collected information on the Internet , Tried to configure : I feel that some experts write well . The configuration is as follows : above ...

  8. of std::map and std::vector Use

    First say map Well . The most important thing to pay attention to is : Access... With subscripts map When an element in , And using subscripts to access vector Their behavior is quite different ! When accessing nonexistent elements with subscripts , Will lead to in map Add a new element to the container , Its key is the subscript ! But a lot of times ...

  9. Luogu P4555 [ National Team ] The longest double palindrome string (manacher Line segment tree )

    The question Topic link Sol My approach is more naive.. First manacher Preprocess the length of palindrome string centered on each position . Then enumerate an intermediate position , Now we have to consider that we can cover i - 1 In the palindrome string of The center is the one on the left , And can cover ...

  10. turbo boost - Rui frequency accelerates

    turbo boost It's Intel frequency acceleration technology Intel Core acceleration technology is Intel Core i7/i5 Unique features of the processor , It's also a new technology announced by Intel . This technology can be understood as automatic overclocking . When Rui frequency acceleration is turned on ,CPU Will be based on the current ...