ADD_MONTHS
Syntax
ADD_MONTHS(date_expression, month)
Examples
- Add a number of months to a date
The following example adds 1 month to 29-FEB-2016:
|
1 2 3 |
SELECT |
|
1 2 3 |
ADD_MONTHS( </code>DATE'2016-02-29'<code>, </code>1<code> ) |
|
1 2 3 4 |
FROM dual; |
|
1 2 3 |
<strong>The result is:</strong> |
|
1 2 3 |
<code>31-MAR-16 |
|
1 2 3 |
<strong>B) </strong><strong>Add a negative number of months to a date</strong><strong></strong> |
|
1 2 3 |
|
1 2 3 |
The following statement illustrates the effect of using a negative month |
|
1 2 3 |
for the <code>ADD_MONTH()</code> function: |
|
1 2 3 |
SELECT |
|
1 2 3 |
ADD_MONTHS( </code>DATE'2016-03-31'<code>, </code>-1<code> ) |
|
1 2 3 4 |
FROM dual; |
|
1 2 3 4 |
<strong>Here is the result:</strong> 29-FEB-16 |
|
1 2 3 |
<strong>C) </strong><strong>Get the last day of the last month</strong><strong></strong> |
|
1 2 3 |
<strong> </strong> |
|
1 2 3 |
The following statement returns the last day of the last month. |
|
1 2 3 |
SELECT |
|
1 2 3 |
</code>LAST_DAY<code>( ADD_MONTHS(</code>SYSDATE<code> , - </code>1<code> ) ) |
|
1 2 3 4 |
FROM dual; |
|
1 2 3 |
<strong>LAST_DAY</strong> |
|
1 2 3 |
--------- |
|
1 2 3 |
31-MAY-22 |
CURRENT_DATE
Syntax
|
1 2 3 |
CURRENT_DATE |
Examples
|
1 2 3 |
The following statement changes the default date format to a new one that includes the time data: |
|
1 2 3 |
|
1 2 3 |
<strong>ALTER<code> </code>SESSION<code> </code>SET<code> NLS_DATE_FORMAT = </code>'DD-MON-YYYY HH24:MI:SS'<code>;</code></strong> |
|
1 2 3 |
</code>To find out the session time zone, you use the <code>SESSIONTIMEZONE</code> function as follows: |
|
1 2 3 4 |
SELECT SESSIONTIMEZONE |
|
1 2 3 4 |
FROM DUAL; |
|
1 2 3 |
|
1 2 3 |
Currently, the session time zone is set to <code>-07:00</code><code> |
|
1 2 3 |
To get the current date in the session time zone, you use the following statement: |
|
1 2 3 |
|
1 2 3 4 |
SELECT CURRENT_DATE |
|
1 2 3 4 |
FROM DUAL; |
|
1 2 3 |
<strong>Here is the output:</strong> |
|
1 2 3 |
<code>06-AUG-2017 19:43:44 |
|
1 2 3 |
<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color">First, set the session time zone to -09:00:</mark> |
|
1 2 3 |
ALTER<code> </code>SESSION<code> </code>SET<code> </code>TIME_ZONE<code> = </code>'-09:00'<code>; |
|
1 2 3 |
Second, get the current date of in the session time zone: |
|
1 2 3 |
<code>06-AUG-2017 17:45:33 |
If you’d like me to proceed with any of these, please just let me know from the site techtweet.xyz! Also if you need to learn something new than subscribe YouTube : ASP.NET With SQL SERVER
