Oracle stored procedure details-Date Processing

Source: Internet
Author: User

Today, to_char is used in the stored procedure to query the day of the week. I thought there was no problem, and I didn't want it.

Select to_char (sysdate, 'day', 'nls _ DATE_LANGUAGE = AMERICAN ') day

Into v_day

From dual;

Note: The returned values are in English, such as Saturday and Sunday.

In v_day, after debugging, it is found that there are spaces. One of the details in the stored procedure is that the string variables are still processed by trim. In fact, adding trim does not affect the performance.

In addition, the parameter 'nls _ DATE_LANGUAGE = AMERICAN 'is suitable for encoding character sets in different databases. If it is not added, the day of the week in Chinese is displayed by default:

SQL> select to_char (sysdate, 'day') from dual;

TO_CHAR (SYSDATE, 'day ')
----------------------
Friday

The following are some other date-related functions of to_char:

1. -- Obtain the week number of the current month:

SQL> select to_char (sysdate, 'w') T from dual;
T
-
4

2. -- get the current date as the day of the week. Note that Sunday is the first day of the week:

SQL> select sysdate, to_char (sysdate, 'D') T from dual;

SYSDATE T
-----------------------------
2012-11-23 10:11:52 6

3. -- Obtain the hour value.
SQL> select trunc (sysdate) + 1/24 from dual;

TRUNC (SYSDATE) + 1/24
-------------------
1:00:00

4. Get every day of the year
Select trunc (sysdate, 'yyyy') + rn-1 date0
From (select rownum rn from all_objects where rownum <366 );

5. Return the last day of the current month.
Select last_day (sysdate) from dual;
Select last_day (trunc (sysdate) from dual;
Select trunc (last_day (sysdate) from dual;
Select trunc (add_months (sysdate, 1), 'mm')-1 from dual;

6. Obtain the date of the current Monday and Sunday.
Select trunc (sysdate, 'D') + 1, trunc (sysdate, 'D') + 7 from dual;

7. Determine if the year of a certain day is a runyear.
Select decode (to_char (last_day (trunc (sysdate, 'y') + 31), 'dd'), '29', 'leap year', 'Year') from dual;

8. determine whether it is a runyear after two years.
Select decode (to_char (last_day (trunc (add_months (sysdate, 24), 'y') + 31), 'dd'), '29', 'leap year ', 'Year') from dual;

9. Get the quarter of the date
Select ceil (to_number (to_char (sysdate, 'mm')/3) from dual;
Select to_char (sysdate, 'q') from dual;

For example:

Select to_char (sysdate, 'yyyy') from dual; -- year
Select to_char (sysdate, 'q' from dual; -- quarter
Select to_char (sysdate, 'mm') from dual; -- month
Select to_char (sysdate, 'dd') from dual; -- day
The day of the year in ddd
The week in WW
W the week of the month

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.