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