Oracle Date Usage

1. Get system time, current time

Select sysdate from dual

2. Date function

When using date calculations in SQL statements, you must use a Date function to convert the string to a date type, unlike using a string directly in SQL Server.

to_date ("string", "converted format")

To_char (date, "convert format") converts the given date according to "conversion format"

Such as:

Query column dcolum (date column) less than 2012-12-1 of data
Select dcolum<to_date (' 2012-12-1 ', ' yy-mm-dd ') from table

3. Date Plus and minus operation

Sysdate+/-1 Plus/minus one day

SYSDATE+/-1/24 Plus/minus 1 hours

SYSDATE+/-1/(24*60) plus/minus 1 minutes

sysdate+/minus 1/(24*60*60) plus/minus 1 seconds

Analogy to milliseconds 0.001 seconds

Add_months (Sysdate,+/-number) plus/minus designated month

Select sysdate+1 from dual

Select add_months (sysdate,12) from dual plus one year

4. Format mask for conversion

　　Classic usage: ' Yyyy-mm-dd hh24:mi:ss ' means: ' 2015-09-23 16:05:00 '

bold is common format, others for reference

yyTwo digits two-year
YYY three digits three-year
yyyyFour digits four-year

Month:
mmNumber two-month
Mon abbreviated Character Set representation
Month spelled out character set representation

Day:
DDNumber of days in the month
DDD Number The first day of the year
DY abbreviated when in for a few days
Day spelled out when in days are all written

Hour:
HH digits 12-hour binary
hh2424-hour binary digits

Minute:
miDigits 60 binary

Second:
SSDigits 60 binary

Other
Q Digit Quarter
WW Digit The first few weeks of the year
W Digit Week of the month

More......

Oracle Date Usage