OraFAQ Forum: SQL & PL/SQL » How to get weeknum for the given date

Home » SQL & PL/SQL » SQL & PL/SQL » How to get weeknum for the given date

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to get weeknum for the given date [message #643529]

Fri, 09 October 2015 15:43

blyzz
Messages: 10
Registered: October 2015

Junior Member

I am trying to get weeknum for the given dates with Monday being the start day for the week.

I can use below function but I need Monday as the start date
TO_CHAR(to_date(tbl1.date),'ww') AS WEEKNUM

Thanks,
Blyzzard

Report message to a moderator

Re: How to get weeknum for the given date [message #643530 is a reply to message #643529]

Fri, 09 October 2015 16:56

flyboy
Messages: 1903
Registered: November 2006

Senior Member

It depends on your exact definition of week, as it will span over two calendar years in December/January.

You may use IW format modifier if it fits the ISO standard (taken from Oracle documentation for 11gR2; you may try consulting the one for your Oracle version from now on): http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch9sql.htm#NLSPG448

By the way, TO_DATE function has (at least) two parameters. Using just one of them uses NLS_DATE_FORMAT for implicit conversion and begs for troubles when it changes (and it will, in the worst time).

Report message to a moderator

Re: How to get weeknum for the given date [message #643534 is a reply to message #643530]

Sat, 10 October 2015 01:28

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

... In addition, TO_DATE on a DATE is an error and implies an additional implicit conversion.

@blyzz

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: How to get weeknum for the given date [message #643580 is a reply to message #643534]

Mon, 12 October 2015 14:11

blyzz
Messages: 10
Registered: October 2015

Junior Member

I want to get the weeknum for the date range Oct2014-Sep 2015
so I want Monday oct 6 2014 as first week and so on.

select NEXT_DAY(TRUNC(TO_DATE('10/01/2014','MM/DD/YYYY'),'WW')-1,'MONDAY') + (1 - 1) * 7 FROM DUAL
I am getting the starting day for the week.

How can I use this to specify in my query if the mydate falls between NEXT_DAY(TRUNC(TO_DATE('10/01/2014','MM/DD/YYYY'),'WW')-1,'MONDAY') + (1 - 1) * 7 and
NEXT_DAY(TRUNC(TO_DATE('10/01/2014','MM/DD/YYYY'),'WW')-1,'MONDAY') + (1 - 1) * 7 then weeknum=1, if next week then weeknum=2 and so on?

Thanks,
Blyzz

Report message to a moderator

Re: How to get weeknum for the given date [message #643581 is a reply to message #643580]

Mon, 12 October 2015 14:17

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you want the week number starting with week 1 day 1 on oct 6 2014 then just subtract the date with this one minus 1 and divide by 7.

Report message to a moderator

Previous Topic:	single row subquery returns more than one row
Next Topic:	groups in regexp_instr

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 24 05:05:06 CDT 2025