Oracle's week date function that meets Chinese habits

Oracle's date function compute week is the first day of Sunday and the last day of Sunday. The Chinese are used to counting the first day on Monday and the last day on Sunday. In addition, Oracle Functions

Oracle's date function compute week is the first day of Sunday and the last day of Sunday. The Chinese are used to counting the first day on Monday and the last day on Sunday. In addition, Oracle Functions

Recently, we helped HR capture the overtime records. HR wants to calculate the number of overtime hours by week. However, Oracle's date function compute week is the first day of Sunday and the last day of Sunday. The Chinese are used to counting the first day on Monday and the last day on Sunday. In addition, Oracle functions return the week number, which is a number, and HR also calculates the date segment based on the data. I wrote a small function that meets our requirements. The returned values are also much more scientific!

FUNCTION get_wk (p_date in date)

RETURN VARCHAR2

IS

V_wk VARCHAR2 (30 );

V_n NUMBER;

BEGIN

SELECT TO_NUMBER (TO_CHAR (p_date, 'D '))

INTO v_n

From dual;



IF v_n = 1

THEN

V_wk: =

TO_CHAR (p_date-6, 'yyyy/MM/dd ')

| '-'

| TO_CHAR (p_date, 'yyyy/MM/dd ');

ELSE

V_wk: =

TO_CHAR (p_date + (7-(5 + v_n), 'yyyy/MM/dd ')

| '-'

| TO_CHAR (p_date + (7-(5 + v_n) + 6, 'yyyy/MM/dd ');

End if;



RETURN v_wk;

END;

Related reading:

Replace () for Oracle Functions ()

Oracle Functions

Differences in case and decode usage and Performance Comparison of Oracle Functions

Simple Oracle functions and stored procedures

The Oracle function obtains the time period in seconds or minutes.

,