Oracle: Time Calculation

Welcome to the Oracle community forum, and interact with 2 million technical staff to enter [3] calculate the number of months with different dates: Generally, the result of two time subtraction is in the unit of days, however, sometimes we want to get results in the unit of month. If manual conversion is too troublesome, Oracle provides another function, which is the mont function.

Welcome to the Oracle community forum, and interact with 2 million technical staff> go to [3] To find the number of months with different dates: Generally, the result of two time subtraction is in the unit of days, however, sometimes we want to get results in the unit of month. If manual conversion is too troublesome, Oracle provides another function, which is the mont function.

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

[3] calculate the number of months with different dates:

Generally, the result is in the unit of days after the two time subtraction, but sometimes we want to get the result in the unit of month. If manual conversion is too troublesome, therefore, Oracle provides another function, which is months_between.

SQL> select months_between (sysdate,

2 to_date ('2017-01-01 01:00:00 ', 'yyyy-mm-dd hh: mi: ss') result

3 from dual;

RESULT

5.94928203

The months_between function has two parameters. The first parameter is the end date and the second parameter is the start date. Oracle uses the first parameter minus the second parameter to obtain the number of months. Therefore, the result may be negative.

4. add or subtract a year:

Oracle does not directly provide the addition and subtraction functions for the year, but with the add_months and months_between functions, we can do the same.

[1] add 2 years to the current date:

SQL> select add_months (sysdate, 2*12) two_years_later

2 from dual;

TWO_YEARS _

30-6 months-10

[2] calculate the difference between the two dates for several years:

SQL> select months_between (sysdate,

2 to_date ('1970-06-30 ', 'yyyy-mm-dd')/12 years_between

3 from dual;

YEARS_BETWEEN

2

Directly subtract two dates and divide them by 365 days is not accurate, but no matter how many days a year, it is always only 12 months, so we can first find the number of months with the difference between the two dates, divide by 12 to get the number of years of difference.

5. the last day of each month:

SQL> select last_day (add_months (sysdate, 2) last_day

2 from dual;

LAST_DAY

September 31-08

6. The first day of each month:

Oracle provides last_day so that we can find the last day of the month, but there is no corresponding first_day function. If you need this function, just use the last_day function. For example, the following SQL statement is used to find the first day of the next month:

SQL> select last_day (sysdate) + 1 fisrt_day

2 from dual;

FISRT_DAY

Month 1-7-08

Here we will convert "the first day of every month" to "the next day of the last day of last month", and the problem will be solved!

7. Calculate the day of the next week:

Sometimes we encounter "what is the number next Friday? "This is a common problem. Oracle provides a function: next_day. Its syntax is as follows: next_day (date, string ). The first parameter date indicates the start time of Oracle, and the second parameter string indicates the business day of Oracle.

Next let's take a look at how to get the Date of next Friday:

SQL> select next_day (sysdate, 'Friday') "Next Friday" from dual;

Select next_day (sysdate, 'Friday') "Next Friday" from dual

*

ERROR at line 1:

ORA-01846: Day in week is invalid

Strange! Yes? The syntax is correct, but why does it say "the day of the week is invalid? Here we have to talk about the language and Time Zone issues in Oracle. The following figure shows the language and Time Zone of the Client session intercepted by TOAD:

　　

We know that the client language is simplified Chinese and the date language is simplified Chinese. This is why the preceding SQL statement is incorrect, because only "Monday, on Tuesday, the workday format is similar to Monday and Firday!

SQL> select next_day (sysdate, 'Friday') "next Friday" from dual;

Next Friday

04-7-08

If you are not sure about your own time zone or you are worried about migrating from one time zone to another, the SQL statement will go wrong. Oracle also allows you to express the work day in numbers. But remember: 1 indicates Sunday, 2 indicates Monday, 3 indicates Tuesday, and so on.

For example, if I want to check the time of Wednesday, the function is written as follows: next_day (sysdate, 4 ).

SQL> select next_day (sysdate, 4) from dual;

NEXT_DAY (S

September 7-08

[1] [2]