Shulou(Shulou.com)06/01 Report--

This article mainly introduces what the date function in Oracle has, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

Date function SYSDATE

The SYSDATE function takes no arguments and returns the current system date and time of the database server.

Select sysdate from dual

Date operation

Date1-Date2=Num1

You can subtract the log from another date. The difference between these two date items indicates the number of days between them. All numbers, including decimals, can be added to or subtracted from a date entry. In this context, numbers represent the number of days. The sum or difference between a number and a date item always returns a date item. Addition, multiplication, or division of two date items are not allowed.

Select to_date ('31MJANMY01')-to_date (' 01MJANMULY01') from dual

Select sysdate + 1 from dual

MONTHS_BETWEEN

The MONTHS_BETWEEN function returns a numeric value that represents the number of months between two mandatory date parameters. Syntax: MONTHS_BETWEEN (date1,date2). Calculate the difference between date1 and date2 (31 days per month). If date1 adds negative numbers before date2. The difference between these two date parameters may consist of integers and decimals. An integer represents the number of friends between the two dates. The decimal part represents the number of days and time remaining after calculating the integer difference between the year and the month, based on the 31-day month. If the day component of the date to be compared is the same or the last day of the respective month, an integer with no decimal part is returned.

Select months_between (sysdate,sysdate-31) from dual

Select months_between ('29murafure 2008) from dual (' 29murmur2008')

Select months_between ('29Muhammar 2008') * 31 from dual

ADD_MONTHS

The ADD_MONTHS function returns a date entry, which is calculated by adding the specified number of months to a given date.

The ADD_MONTHS function has two mandatory arguments. Syntax: ADD_MONTHS (start date,number of months). The function does not calculate the target date until the specified number of months is added to the start date. The number of months may be negative, so the target date returned is earlier than the start date. Number of months can be a decimal, but it ignores the decimal part and uses the integer part.

Select add_months ('07MAPRMI 2009) from dual

Select add_months ('07Mutual APRMU2009) from dual

Select add_months ('07MAPRMI 2009) from dual (September 2009) APRMI 2009

NEXT_DAY

The date returned by the NEXT_DAY function is the date of the next occurrence of the specified day of the week.

The NEXT_DAY function has two mandatory arguments. Syntax: NEXT_DAY (start date,day of the week). Function calculates the date of the next occurrence of the day of the week parameter after start date. The day of the week parameter can be a character value or an integer value. Acceptable values are determined by the NLS_DATE_LANGUATE database parameter, but the default value is at least the first three characters or integer values of the day name, where 1 is Sunday, 2 is Monday, and so on. In any case, you should specify a character value that represents the day of the week. A short name can be longer than three characters, such as sun, sund, sunda, or sunday.

Select next_day ('01MJANMUE 2009') from dual

Select next_day ('01MJANMUE 2009) from dual

Select next_day ('01MJANMI2009) from dual

LAST_DAY

The LAST_DAY function returns the date of the last day of the month to which the specified day belongs.

The LAST_DAY function has a mandatory argument. Syntax: LAST_DAY (start date). This function extracts the month to which the start date parameter belongs and calculates the date of the last day of the month.

Select LAST_DAY ('01MJANMUL 2009') from dual

Date ROUND

The date ROUND function rounds the value according to the specified date precision format. The returned value is either rounded up or down to the nearest date precision format.

The date ROUND function takes a mandatory parameter and an optional parameter. Syntax: ROUND (source date [, date precision format]) The source date parameter represents any date item. The date precision format parameter specifies the precision of rounding, which is optional, and if not specified, the default rounding precision is day. Date precision formats includes century (CC), year (YYYY), quarter (Q), month (MM), week (W), day (DD), hour (HH) and minute (MI).

The upward introduction of the century is equivalent to adding one century to the current century. If the day part is greater than 16, it will be rounded up to the next month, otherwise it will be rounded down to the beginning of the month. If the month is between 1 and 6, rounding to the year returns the date at the beginning of the year, otherwise it returns the date at the beginning of the next year.

Select round (sysdate) day,round (sysdate,'w') week,round (sysdate,'month') month,round (sysdate,'year') year from dual

Select round (sysdate,'cc') cc,round (sysdate,'q') Q magnetic round (sysdate,'hh'), round (sysdate,'mi') min hour from dual

Date TRUNC

The date TRUNC function intercepts the value according to the specified date precision format.

The date TRUNC function takes a mandatory parameter and an optional parameter. Syntax: TRUNC (source date [, date precision format]) The source date parameter represents any date item. The date precision format parameter specifies the precision of the intercept, which is optional, and if not specified, the default intercept precision is day. That is, all time parts of the source date are set to midnight (00:00:00). Interception at the month level sets the date of the source date to the first day of the month. The interception on the grade returns the date at the beginning of the year.

Select trunc (sysdate) day,trunc (sysdate,'w') week,trunc (sysdate,'month') month,trunc (sysdate,'year') year from dual

Thank you for reading this article carefully. I hope the article "what are the date functions in Oracle" shared by the editor will be helpful to you? at the same time, I also hope you will support us and follow the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.