OraFAQ Forum: SQL & PL/SQL » Day number in a Quarter

Home » SQL & PL/SQL » SQL & PL/SQL » Day number in a Quarter

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

Day number in a Quarter [message #225324]

Mon, 19 March 2007 12:40

bjayan
Messages: 5
Registered: March 2007

Junior Member

Made a correction below on the day number returned.

Hi,
How can I get a day number in a quarter using sql or pl/sql?

eg: today is Mar 19, 2007 and this is Quarter 1. Day number returned should be 78. Similary if it was Apr 01, 2007 day number returned should be 01.

TIA,
Jayan

[Updated on: Mon, 19 March 2007 12:55]

Report message to a moderator

Re: Day number in a Quarter [message #225325 is a reply to message #225324]

Mon, 19 March 2007 12:45

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

You may get different responses from others, but I have seen this done many times, including Oracle Applications, and everybody always chooses to create a local business calendar table. The first column is always date. Subsequent columns are the data you need, such fiscal year, quarter, day of quarter, day of year, day of month. This table is prepopulated when the application is set up. It's just easier. I highly recommend not killing yourself trying to make something more "elegant" and/or "flexible", but that is up to you.

Report message to a moderator

Re: Day number in a Quarter [message #225327 is a reply to message #225325]

Mon, 19 March 2007 12:58

bjayan
Messages: 5
Registered: March 2007

Junior Member

but don't you think having a program in place is much better than having to manually enter a value for each of those fields in the calendar table? I know using excel it can be speeded up but it's again manual and error-prone.

Report message to a moderator

Re: Day number in a Quarter [message #225330 is a reply to message #225324]

Mon, 19 March 2007 13:09

MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA

Senior Member

SOrry I posted a solution w/o reading the entire OP..

[Updated on: Mon, 19 March 2007 13:12]

Report message to a moderator

Re: Day number in a Quarter [message #225337 is a reply to message #225324]

Mon, 19 March 2007 13:26

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

Senior Member
Account Moderator

SQL> select trunc(sysdate-trunc(sysdate,'Q'))+1 DayNb from dual;
     DAYNB
----------
        78

1 row selected.

Regards
Michel

[Updated on: Mon, 19 March 2007 13:27]

Report message to a moderator

Re: Day number in a Quarter [message #225352 is a reply to message #225337]

Mon, 19 March 2007 14:00

bjayan
Messages: 5
Registered: March 2007

Junior Member

That was sweet! Thanks a ton.

Have another one - how do I get a week number in a quarter?

TO_CHAR(sysdate, 'WW') gives me the week number for the year but is there a way to get for the quarter?

Report message to a moderator

Re: Day number in a Quarter [message #225354 is a reply to message #225352]

Mon, 19 March 2007 14:04

Frank
Messages: 7901
Registered: March 2000

Senior Member

If you UNDERSTAND Michels previous query, this one is easy.
If you don't understand it, well, study it until you do.

Report message to a moderator

Re: Day number in a Quarter [message #225355 is a reply to message #225324]

Mon, 19 March 2007 14:05

MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA

Senior Member

Stealing from Michel's earlier solution what about

select ceil(to_number(trunc(sysdate-trunc(sysdate,'Q'))+1) / 7) wkNb from dual

Report message to a moderator

Re: Day number in a Quarter [message #225358 is a reply to message #225324]

Mon, 19 March 2007 14:07

Bill B
Messages: 1971
Registered: December 2004

Senior Member

select trunc(to_char(sysdate,'ww') - to_char(trunc(sysdate,'q'),'ww'))+1 DayNb from dual;

Report message to a moderator

Re: Day number in a Quarter [message #225375 is a reply to message #225352]

Mon, 19 March 2007 17:06

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

So your company's fiscal quarter starts on Jan 1. Interesting. I have never seen that before. But there is a first to everything.

Report message to a moderator

Previous Topic:	Whats wrong with these joins? Please help !!!!
Next Topic:	Using the Accept Command

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 21 20:05:48 CDT 2025