Oracle dates common operations and functions

--(Take the system time as an example, the actual situation can be replaced)

1 Read system time –sysdate

Select sysdate from dual;

2 Read time information (year, month, day, hour, minute, second)

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') as nowtime from dual; Date converted to string
Select To_char (sysdate, ' yyyy ') as nowyear from dual; Get the year of the time
Select To_char (sysdate, ' mm ') as nowmonth from dual; Get the month of the time
Select To_char (sysdate, ' DD ') as nowday from dual; Get the day of the time
Select To_char (sysdate, ' hh24 ') as nowhour from dual; When you get the time
Select To_char (sysdate, ' mi ') as Nowminute from dual; Get the minutes of the time
Select To_char (sysdate, ' SS ') as Nowsecond from dual; Gets the seconds of the time

3 Changing the time information (year, month, day, hour, minute, second)

Select Sysdate,sysdate+numtoyminterval (3, ' year ') as res from dual;

Select Sysdate,sysdate+numtoyminterval (3, ' month ') as res from dual;

Select Sysdate,sysdate+numtodsinterval (3, "Day") as res from dual;

Select Sysdate,sysdate+numtodsinterval (3, ' Hour ') as res from dual;

Select Sysdate,sysdate+numtodsinterval (3, ' minute ') as res from dual;

Select Sysdate,sysdate+numtodsinterval (3, ' second ') as res from dual;

Or

Select to_char (sysdate, ' yyyy-mm-ddhh24:mi:ss '), To_char (sysdate+

n*365, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual

Change Time-year
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), Add_months (Sysdate,n) as NewTime from dual

Change Time-month
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char (Sysdate+n,

' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual

Change Time-day
Select To_char (sysdate, ' yyyy-mm-ddhh24:mi:ss '), To_char (SYSDATE+N/24,

' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual

Change Time-When
Select To_char (sysdate, ' yyyy-mm-ddhh24:mi:ss '), To_char (SYSDATE+N/24/60, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual

Change Time-minutes
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char (sysdate+

N/24/60/60, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual

Change Time-seconds

4 Finding the date and time difference

Select Floor (To_number (sysdate-to_date (' 2007-11-0215:55:03 '),

' Yyyy-mm-dd hh24:mi:ss '))/365) as Spanyears from dual

Time difference-year

SELECT Months_between (to_date (' 2011-05-03 '),

' Yyyy-mm-dd '), to_date (' 2011-01-23 ', ' yyyy-mm-dd ')) as spanmonths from dual;//time difference-month

Select Floor (to_number sysdate-to_date (' 2007-11-02-15:55:03 ',

' Yyyy-mm-dd hh24:mi:ss ')) as spandays from dual

Time Difference-day

Select Floor (to_number sysdate-to_date (' 2007-11-02-15:55:03 ',

' Yyyy-mm-dd hh24:mi:ss ')) *24) as spanhours from dual

Time Difference-When

Select Floor (to_number sysdate-to_date (' 2007-11-02-15:55:03 ',

' Yyyy-mm-dd hh24:mi:ss ')) *24*60) as spanminutes from dual

Time difference-minute

Select Floor (to_number sysdate-to_date (' 2007-11-02-15:55:03 ',

' Yyyy-mm-dd hh24:mi:ss ')) *24*60*60) as Spanseconds from dual

Time difference-seconds

5 , trunc (D1[,C1]) function

Return date D1 The first day of the period (parameter C1)

D1 Date Type, C1 is character (parameter), C1 default is J (i.e. current date)

C1 the corresponding parameter table:

This week Sunday: day or Dy or D (Weekly order: Days, one, two, three, four, five, six)

Early Month Date: Month or Mon or mm or RM

Date of the season: Q

Early date: syear or year or yyyy or yyy or yy or y (multiple y means precision)

Beginning of the Century Date: CC or SCC
Select Sysdate from dual--date

Select Trunc (sysdate) from dual

Select Trunc (sysdate, ' DD ') from dual--today's date

Select Trunc (sysdate, ' d ') +7 from dual--Sunday of the week

Select Trunc (sysdate, ' dy ') +7 from dual--Sunday of the week

Select Trunc (sysdate, ' Day ') +7 from dual--Sunday of the week

Select Trunc (sysdate, ' Q ') from dual--this season start date

Select Trunc (sysdate, ' month ') from dual--month start date

Select Trunc (sysdate, ' mm ') from dual--month start date

Select Trunc (Sysdate, ' year ') from dual--start date

Select Trunc (sysdate, ' yyyy ') from dual--year start date

Select Trunc (sysdate, ' HH24 ') from dual-This hour start time

Select Trunc (sysdate, ' MI ') from dual--this minute start time

Select Trunc (sysdate, ' CC ') from dual--time of the century

Select Trunc (Last_day (sysdate), ' DD ') from dual--last day of the month

6 round function

The function rounds the input date to the form specified by the second argument.

Round (to_date (' 1999.11.24 08:37 am ', ' yyyy.mm.dd hh:mi am '), ' hh ')

Back to 1999.11.24 09:00:00 am

Formatting results

SS 1999.11.24 10:31:11

MI 1999.11.24 10:31:00

HH 1999.11.24 11:00:00

DD 1999.11.24 00:00:00

MM 1999.12.01 00:00:00

YY 2000.01.01 00:00:00

7 Last_day

Returns the last day of the month that contains the given date. The syntax is:

Last_day (date)

Select Last_day (' 2 January-February -80 ') from dual;

8 The master summed up very well:

Http://www.blogjava.net/pengpenglin/archive/2008/06/30/211589.html#part7

Oracle dates common operations and functions