Summary and sharing of Oracle date functions

This article brings you relevant knowledge about Oracle, which mainly introduces some commonly used date function-related issues, including SYSDATE, ADD_MONTHS, LAST_DAY, TRUNC, ROUND, etc. I hope Helpful to everyone.

Recommended tutorial: "Oracle Tutorial"

System date and time functions

SYSDATE function

This function has no parameters and can get the current time of the system.

Case code:

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

Result:

##SYSTIMESTAMP function

This function does not have Parameter, you can get the current time of the system, which contains time zone information and is accurate to microseconds.

Case code

select systimestamp from dual;

Result:

Database time zone function

DBTIMEZONE function

This function has no input parameters and returns the database time zone.

Case code:

select dbtimezone from dual;

Result:

## Add the specified month function to the date:

ADD_MONTHS(r,n) function

This function returns the date after adding the number of months n to the specified date r. Where

r: the specified date.

n: The number of months to be added. If N is a negative number, it means the number of months to be subtracted.

Case code:

select to_char(add_months(to_date('2018-11-12', 'yyyy-mm-dd'), 1),
               'yyyy-mm-dd'),
       to_char(add_months(to_date('2018-10-31', 'yyyy-mm-dd'), 1),
               'yyyy-mm-dd'),
       to_char(add_months(to_date('2018-09-30', 'yyyy-mm-dd'), 1),
               'yyyy-mm-dd')
  from dual;

Result: (If the specified date is the last day of the month, the returned last day of the new month is also the last day of the new month. If the new month is less than the specified month date, The valid date will be automatically recalled)

Last day of month function

LAST_DAY(r) function

Returns the last day of the current month for the specified r date.

Case code:

select last_day(sysdate) from dual;

Result:

Date function one week after the specified date:

NEXT_DAY(r,c) function

Returns the date corresponding to the r date character (c: indicating the day of the week) in the week after the specified R date.

Case code:

 select next_day(to_date('2018-11-12','yyyy-mm-dd'),'星期四') from dual;

Result:

Function that returns a specific part of the specified date

EXTRACT(time) function

Returns the year, month, day, and divided date part of the specified time.

Case code:

select extract(year from timestamp '2018-11-12 15:36:01') as year,
       extract(month from timestamp '2018-11-12 15:36:01') as month,
       extract(day from timestamp '2018-11-12 15:36:01') as day,
       extract(minute from timestamp '2018-11-12 15:36:01') as minute,
       extract(second from timestamp '2018-11-12 15:36:01') as second
  from dual;

Result:

Return the number of months between two dates:

MONTHS_BETWEEN(r1,r2) function

This function returns the direct month of r1 date and r2 date. When r1>r2, a positive number is returned. If r1 and r2 are on the same day in different months, an integer is returned, otherwise a decimal is returned. When r1

Case code:

select months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
                      to_date('2017-11-12', 'yyyy-mm-dd')) as zs, --整数
       months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
                      to_date('2017-10-11', 'yyyy-mm-dd')) as xs, --小数
       months_between(to_date('2017-11-12', 'yyyy-mm-dd'),
                      to_date('2018-10-12', 'yyyy-mm-dd')) as fs --负数
  from dual;

Result:

Date interception function

ROUND(r[, f]) Function

rounds the date r according to the format of f. If f is not filled in, it will be rounded to the nearest day.

Case code:

select sysdate, --当前时间
       round(sysdate, 'yyyy') as year, --按年
       round(sysdate, 'mm') as month, --按月
       round(sysdate, 'dd') as day, --按天
       round(sysdate) as mr_day, --默认不填按天
       round(sysdate, 'hh24') as hour --按小时
  from dual;

Result:

##TRUNC(r[,f]) function

Truncate the date r in the format of f. If f is not filled in, the current date will be intercepted.

Case code:

select sysdate, --当前时间
       trunc(sysdate, 'yyyy') as year, --按年
       trunc(sysdate, 'mm') as month, --按月
       trunc(sysdate, 'dd') as day, --按天
       trunc(sysdate) as mr_day, --默认不填按天
       trunc(sysdate, 'hh24') as hour --按小时
  from dual;

Result:

Recommended tutorial: "Oracle Learning Tutorial

"

The above is the detailed content of Summary and sharing of Oracle date functions. For more information, please follow other related articles on the PHP Chinese website!