Home » SQL & PL/SQL » SQL & PL/SQL » Day number in a Quarter
Day number in a Quarter [message #225324] Mon, 19 March 2007 12:40 Go to next message
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 Go to previous messageGo to next message
scottwmackey
Messages: 505
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.
Re: Day number in a Quarter [message #225327 is a reply to message #225325] Mon, 19 March 2007 12:58 Go to previous messageGo to next message
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.
Re: Day number in a Quarter [message #225330 is a reply to message #225324] Mon, 19 March 2007 13:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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?
Re: Day number in a Quarter [message #225354 is a reply to message #225352] Mon, 19 March 2007 14:04 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Day number in a Quarter [message #225355 is a reply to message #225324] Mon, 19 March 2007 14:05 Go to previous messageGo to next message
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
Re: Day number in a Quarter [message #225358 is a reply to message #225324] Mon, 19 March 2007 14:07 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
select trunc(to_char(sysdate,'ww') - to_char(trunc(sysdate,'q'),'ww'))+1 DayNb from dual;
Re: Day number in a Quarter [message #225375 is a reply to message #225352] Mon, 19 March 2007 17:06 Go to previous message
scottwmackey
Messages: 505
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.
Previous Topic: Whats wrong with these joins? Please help !!!!
Next Topic: Using the Accept Command
Goto Forum:
  


Current Time: Fri Dec 02 17:01:47 CST 2016

Total time taken to generate the page: 0.31024 seconds