All geek questions in one place

How can I allocate a week in Oracle using a custom first day of the week?

I have a request that should return the "week of the year" date fields, but the request client uses a non-standard first day of the week, so TO_CHARwith 'IW'does not return the expected result. In this case, the first day of the week is Saturday, and Friday is the seventh day of the week.

With the T-SQL I would use DATEPARTand the SET DATEFIRST .

What is the equivalent of Oracle? The answers of Oracle that I found on google talk about customization NLS_TERRITORY, just like ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM';I do, but I don’t see where I can choose any day (except maybe finding a territory using Saturday).

+3
source share
2 answers

IW works weekly-Sunday, so you need to get what you are looking for. Basically, get a week depending on the day in 2 days:

to_char(your_date + 2, 'IW')
+3
source

Default Week Format Oracle calculates the week number from the first day of the year instead of the first day of the week.

So, if the year begins on January 1, 2009, and on the first day on Wednesday, week No. 1 will be from January 01, 2009 to January 08-2009 (Wednesday on Tuesdays).

You can use the “iw” format (and a little customization) if you need a range of weeks to start from Sunday through Saturday. http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10749/ch9sql.htm#CIHGFJEI

. "IW" , .. ... 01-jul-2008.

select target_date, 
        to_char(target_date+1,'iw') week_sun_thru_saturday,
        to_number(to_char(target_date+1,'iw')) -
        to_number(to_char( to_date('10-jul-2008','dd-mon-yyyy')+1,'iw')) week_from_01_jul_2008
from t;

... 1 jul1st jul-07. , 01-jul-2008 - ;)

+1

Source: https://habr.com/ru/post/1773340/

More articles:


All Articles