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
- 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 ...
- 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- ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- oracle Time function
Add select sysdate,add_months(sysdate,12) from dual; -- Add 1 year select sysdate,add_months(sysdate,1) from ...
- [ 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 ...
- [ 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
- 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 ...
- 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 ...
- 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 ...
- 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) ...
- 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 ...
- Merge Into example
merge into users a using temp_users b on (a.userid = b.user_id) when matched then update set a.passw ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...