Home » SQL & PL/SQL » SQL & PL/SQL » get weekday (Sql,9.2.0.8.0,XP)
get weekday [message #431562] Wed, 18 November 2009 03:32 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

My query is:

how can i get listing of all week(start day = Monday,end day=Saturday)of a current month.

1st week =02-Nov-2009~07-Nov-2009
2nd week =09-Nov-2009~14-Nov-2009
3rd week =16-Nov-2009~21-Nov-2009
4th week =23-Nov-2009~28-Nov-2009

when user gives value =1st week then system goto 3 months back and display records of the weekend accordingly like this:

if user select/input 3rd week then system goto ro_mst table and show all data range from 16-Aug-2009 to 21-Aug-2009.

I am using the following query (get from PL/SQL forum) to show weekend but its not working as desired.

Select  'Week '|| to_char(SYSDATE,'IW') 
  from DUAL
 where to_char(sysdate,'MON,YY')='NOV,09';


Result is
'WEEK'|
-------
Week 47




Regards


Zuhair


Re: get weekday [message #431564 is a reply to message #431562] Wed, 18 November 2009 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> Select  'Week '|| to_char(SYSDATE,'W')
  2    from DUAL
  3   where to_char(sysdate,'MON,YY')='NOV,09';
'WEEK'
------
Week 3

1 row selected.

Regards
Michel
Re: get weekday [message #431572 is a reply to message #431564] Wed, 18 November 2009 04:18 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thank you Michel Cadot ,the query is working fine,could you please tell how can i get all weeks of the current month.
my desirable result is:
Week     from date   to date

week 1 =02-Nov-2009 07-Nov-2009
week 2 =09-Nov-2009 14-Nov-2009
week 3 =16-Nov-2009 21-Nov-2009
week 4 =23-Nov-2009 28-Nov-2009


All the above data will come out from dual table.


Regards


Zuhair



Re: get weekday [message #431574 is a reply to message #431572] Wed, 18 November 2009 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What should be the result for 2-OCT-2009

Regards
Michel
Re: get weekday [message #431575 is a reply to message #431562] Wed, 18 November 2009 04:41 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
The result of oct,2009 will be:

Week     from date   to date

week 1 =01-Oct-2009 03-Oct-2009
week 2 =05-Oct-2009 10-Oct-2009
week 3 =12-Oct-2009 17-Oct-2009
week 4 =19-Oct-2009 24-Oct-2009
week 4 =26-Oct-2009 31-Oct-2009



Re: get weekday [message #431577 is a reply to message #431575] Wed, 18 November 2009 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> Select  'Week '|| 
  2          to_char(to_number(to_char(SYSDATE,'W'))
  3                  - decode(to_char(trunc(sysdate,'MONTH'),'fmDAY'),'SUNDAY',1,0))
  4  from DUAL
  5  where to_char(sysdate,'MON,YY')='NOV,09'
  6  /
'WEEK'||TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'W'
---------------------------------------------
Week 2

1 row selected.

Regards
Michel
Re: get weekday [message #431578 is a reply to message #431562] Wed, 18 November 2009 05:03 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks you Michel for your reply,at this time result is not desirable because if we select or input Nov,09 then all week of November should be display like this :

In case of Nov,09:

The result is :
week 1 =02-Nov-2009 07-Nov-2009
week 2 =09-Nov-2009 14-Nov-2009
week 3 =16-Nov-2009 21-Nov-2009
week 4 =23-Nov-2009 28-Nov-2009


In case of Oct,09:

The result is :
week 1 =01-Oct-2009 03-Oct-2009
week 2 =05-Oct-2009 10-Oct-2009
week 3 =12-Oct-2009 17-Oct-2009
week 4 =19-Oct-2009 24-Oct-2009
week 4 =26-Oct-2009 31-Oct-2009

Actually i want to create such type of Report that contain a parameter showing all week of the current month and after selection of desired week the system automatically goback to 3 month back and show data comes from ro_mst accordingly.

Suppose user input 3 week and the current month is November then system automatically get all data from ro_mst table range from 16-Aug-2009 to 21-Aug-2009.
Re: get weekday [message #431579 is a reply to message #431578] Wed, 18 November 2009 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Suppose user input 3 week and the current month is November then system automatically get all data from ro_mst table range from 16-Aug-2009 to 21-Aug-2009.

Post what you tried from what I posted.

In addition, your requirements are not consistent:
Quote:
if we select or input Nov,09 then all week of November should be display like this :

In case of Nov,09:

The result is :

week 1 =02-Nov-2009 07-Nov-2009
week 2 =09-Nov-2009 14-Nov-2009
week 3 =16-Nov-2009 21-Nov-2009
week 4 =23-Nov-2009 28-Nov-2009

Quote:
Suppose user input 3 week and the current month is November then system automatically get all data from ro_mst table range from 16-Aug-2009 to 21-Aug-2009.

Why August? Assuming this is Novemver, what does it should be? The former or the latter?

Regards
Michel
Re: get weekday [message #431585 is a reply to message #431562] Wed, 18 November 2009 06:02 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
I am very sorry if i am not able to deliver my actual requirement.

If user input 3 week or 3rd week and the current month is November,2009 then system automatically goto 3 month back and display data.

i.e.
if user select 3 week or 3rd week of nov,2009 (3 week of November means 16 Nov,09 to 21 Nov,2009) then system automatically subtract -3 month from November that is August,2009 and show data of August from ro_mst table range from 16 Oct,09 to 21 Oct,2009.(range equal to 3 week of November).







[Updated on: Wed, 18 November 2009 06:06]

Report message to a moderator

Re: get weekday [message #431586 is a reply to message #431585] Wed, 18 November 2009 06:06 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah, another one of those.

- I need X
- Do *Thing*
- That doesn't work, because Y
- Do *Thing*
- That doesn't work, because Q
- Do *Thing*
- That doesn't work, because Z

And now you want to display August, even though the user selected November?

Can you please post a COMPLETE specification of what you actually want?
Re: get weekday [message #431734 is a reply to message #431562] Thu, 19 November 2009 05:58 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Sorry if i am unable to communicate my scenaio to you,kindly just help me if it is possible to generate such type of query through which i can get all the weeks of the curent month with their date range.

If Current month is Nov,2009 then
desired output is:
week 1 02-Nov-2009  07-Nov-2009
week 2 09-Nov-2009  14-Nov-2009
week 3 16-Nov-2009  21-Nov-2009
week 4 23-Nov-2009  28-Nov-2009


If Current month is Oct,2009 then
desired output is:
week 1 01-Oct-2009  03-Oct-2009
week 2 05-Oct-2009  10-Oct-2009
week 3 12-Oct-2009  17-Oct-2009
week 4 19-Oct-2009  24-Oct-2009
week 4 26-Oct-2009  31-Oct-2009


Regards


Zuhair





[Updated on: Thu, 19 November 2009 06:13]

Report message to a moderator

Re: get weekday [message #431741 is a reply to message #431734] Thu, 19 November 2009 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what comes to:
Quote:
system automatically subtract -3 month from November that is August...

and to:
Quote:
then system automatically get all data from ro_mst table range from ...


Now suppose I give "Nov 13, 2009", post the output and only that.

Regards
Michel
Re: get weekday [message #431745 is a reply to message #431734] Thu, 19 November 2009 06:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We're sorry you can't explain what you want too.
select min(trunc(dte,'MON')+(level-1)) week_start
      ,max(trunc(dte,'MON')+(level-1)) week_end
      ,to_char(trunc(dte,'MON')+(level-1),'W') week
FROM   (select sysdate dte 
        from   dual)
CONNECT BY LEVEL <= (last_day(dte) - trunc(dte,'MON')+1)
group by to_char(trunc(dte,'MON')+(level-1),'W')
order by week;

Re: get weekday [message #431752 is a reply to message #431741] Thu, 19 November 2009 06:23 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
user cannot give any input by using dates,user can only give input in the form of week1,week2,week3.....and so on.



Re: get weekday [message #431757 is a reply to message #431752] Thu, 19 November 2009 06:39 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Why cant you use JRowbottom Query to get your desired output? You Can modify the query to meet your requirement...Right?

sriram Smile
Re: get weekday [message #431762 is a reply to message #431757] Thu, 19 November 2009 06:45 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
hmmm,will try to modify it and then let your people know.
Re: get weekday [message #431768 is a reply to message #431752] Thu, 19 November 2009 07:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
gozuhair wrote on Thu, 19 November 2009 13:23
user cannot give any input by using dates,user can only give input in the form of week1,week2,week3.....and so on.

What is the input?
If it is 3 (for week 3) but week 3 of what?
What is the output, if input is 3?

Regards
Michel

Re: get weekday [message #431774 is a reply to message #431768] Thu, 19 November 2009 07:12 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
if user select/input 3rd week then system goto ro_mst table and show all data range from 16-Aug-2009 to 21-Aug-2009.

i guess this is his requirement....
the result based on current date,if the client selected "week3" then he should display the August 3rd week date range(16-Aug-2009 to 21-Aug-2009)..(November to August 3 months)


And @ gozuhair

16-Aug is Sunday for me(+5.30) Not monday.

sriram Smile

[Updated on: Thu, 19 November 2009 07:15]

Report message to a moderator

Re: get weekday [message #431776 is a reply to message #431774] Thu, 19 November 2009 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i guess this is his requirement....
the result based on current date,if the client selected "week3" then he should display the August 3rd week date range..(November to August 3 months)

Pure guess as it is clear as mud what are OP's requirements. Even him can't say what they are.

Regards
Michel
Re: get weekday [message #431777 is a reply to message #431776] Thu, 19 November 2009 07:19 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel ,Please see this ,if this is what you are asking for...
This is what he said....

Quote:
Dear All

My query is:

how can i get listing of all week(start day = Monday,end day=Saturday)of a current month.

1st week =02-Nov-2009~07-Nov-2009
2nd week =09-Nov-2009~14-Nov-2009
3rd week =16-Nov-2009~21-Nov-2009
4th week =23-Nov-2009~28-Nov-2009

when user gives value =1st week then system goto 3 months back and display records of the weekend accordingly like this:

if user select/input 3rd week then system goto ro_mst table and show all data range from 16-Aug-2009 to 21-Aug-2009.

I am using the following query (get from PL/SQL forum) to show weekend but its not working as desired.



And I am sorry for quoting entire message...Wink

[Updated on: Thu, 19 November 2009 07:20]

Report message to a moderator

Re: get weekday [message #431779 is a reply to message #431777] Thu, 19 November 2009 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is what he said....

Among many other things not consistent with this.

Regards
Michel
Re: get weekday [message #432078 is a reply to message #431562] Fri, 20 November 2009 23:17 Go to previous message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
I have modfied JRowbottom query according to my needs,thanks for helping me.
Previous Topic: How can i lock a table to restrict from selection for another user (merged)
Next Topic: i got a query to solve
Goto Forum:
  


Current Time: Thu Dec 08 06:24:41 CST 2016

Total time taken to generate the page: 0.11050 seconds