Oracle Date Comparison ____oracle

System time is the time to send a blog, that is, 2012-8-5.

Comparison of the 1st period

SELECT * from dual where sysdate > to_date (' 2000.8.5 ', ' yyyy.mm.dd ')

SELECT * from dual where to_date (' 2012.8.5 ', ' yyyy.mm.dd ') > To_date (' 2012.6.5, ' yyyy.mm.dd ')

SELECT * from dual where to_date (' 2012.8.5 ', ' yyyy.mm.dd ') = to_date (' 2012.8.5 ', ' yyyy.mm.dd ')

Subtract on date of 2nd

Select (Sysdate-to_date (' 2013.3.1 ', ' yyyy.mm.dd ')) from dual

Results-207.628275462963

The result is the difference in number of days, and the difference in hours in seconds is also converted to days

3rd period Distance

SELECT * from dual where sysdate

Between To_date (' 2008-08-05 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

and To_date (' 2013-08-05 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

4 Other Applications

Get Yesterday

Select Trunc (SYSDATE-1) from dual;

2012-8-4

Check it out.

Select To_char (trunc (SYSDATE-1), ' Yyyy-mm-dd HH24:MI:SS ') from dual;

Results 2012-08-04 00:00:00

get the first day of last month 00:00:00

Select Add_months (trunc (sysdate, ' MON '), -1) from dual

Select Add_months (trunc (sysdate, ' MON '), -13) from dual

Results 2012-7-1

get today 00:00:00 last month

Select Trunc (add_months (sysdate,-1)) from dual

Results 2012-7-5

get the first day of the quarter

Select TRUNC (Add_months (sysdate,0), ' Q ') from dual

Results 2012-7-1

get the first day of the last quarter

Select TRUNC (Add_months (sysdate,-3), ' Q ') from dual

Results 2012-4-1

obtained last January 1

To_char (Add_months (trunc (Sysdate, ' year '), -12), ' Yyyy-mm-dd ')

Results 2011-01-01

Subtract

To_date (' 20110105 ', ' YYYYMMDD ')-7 here 7 stands for the day

Results 2010-12-29

Original post address: http://www.cnblogs.com/xd502djj/archive/2010/08/24/1807565.html