Is there any function in sql to extract week?

I want to extract week from datetime, the output I want is 'YY/week', where week is the week of the year (eg '201724' is the 24th week in 2017).

Solution 1:

The term "week of the year" is too ambiguous.

1. The week may start from Sunday, Monday or another weekday
2. The weeks enumeration in the year may start from 0 or 1
3. The weeks enumeration in the year may start from the week which includes January, 1 (and hence may be partial) or from first complete week of the year
4. The last week of the year, if it is partial, may be counted or not

Each DBMS has its own functions (sometimes original, always with original names) that can return the number of the week in the year on a given date. But they can not always take into account the above features.

Important addition provided by jarlh:

ISO 8601 (#4.3.4):

• The first calendar week of a year is the one that includes the first Thursday of that year.
• The last calendar week of a calendar year is the week immediately preceding the first calendar week of the next calendar year.
• Week 1 is the first week of a year.
• A calendar week starts on a Monday.

ISO 9075 doesn't even mention weeks.

Solution 2:

SELECT TO_CHAR(TO_DATE('19-FEB-22') , 'IW') from DUAL;

To get the corresponding four-digit year, use

SELECT TO_CHAR(TO_DATE('19-FEB-22'), 'IYYY') FROM DUAL;

TO_CHAR() having so many options like this read more in Oracle manual or extract portation of date Extract Portion of Date Time Value

OutPut