Home » SQL & PL/SQL » SQL & PL/SQL » Get all days for a month, along with Week, Day when first, last day are passed (merged 5)
Get all days for a month, along with Week, Day when first, last day are passed (merged 5) [message #389365] Sun, 01 March 2009 10:52 Go to next message
rahulnair
Messages: 2
Registered: March 2009
Junior Member
Hi Gurus,

I am trying to write an SQL where in if I pass the first date and last date of the month, it gets me all the days in the month with the 'WW' week, and 'Day'. The sql I am trying is,

select b.e, to_char(b.e,'WW'), to_char(b.e,'Day') from
  (SELECT distinct TRUNC(task_DATE) EXP_DATE  from task_summary
     WHERE to_char(task_DATE,'MON-YYYY') = 'DEC-2009'
     ) A,(SELECT (to_date('01-DEC-2009','DD-MON-YYYY') + rownum) e
  FROM (SELECT 1 from DUAL CONNECT BY level <= ( to_date('31-DEC-2009','DD-MON-YYYY') - to_date('01-DEC-2009','DD-MON-YYYY'))
  )) b
  where a.exp_date(+) = b.e
  order by b.e


But the Week seems to be incorrect here, not sure if the sql is incorrect. Can someone please help!

Thanks
Re: Get all days for a month, along with Week, Day when first, last day are passed (merged 3) [message #389369 is a reply to message #389365] Sun, 01 March 2009 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Don't multipost.
2/ Use SQL Formatter.
3/ What is task_summary?
4/ What does "Week seems to be incorrect" means?

Regards
Michel

[Updated on: Sun, 01 March 2009 11:02]

Report message to a moderator

Re: Get all days for a month, along with Week, Day when first, last day are passed [message #389371 is a reply to message #389365] Sun, 01 March 2009 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

STOP POSTING YOUR QUESTION.

Regards
Michel
Re: Get all days for a month, along with Week, Day when first, last day are passed [message #389375 is a reply to message #389371] Sun, 01 March 2009 11:10 Go to previous messageGo to next message
rahulnair
Messages: 2
Registered: March 2009
Junior Member
Apologies!!! The page would just hang after hitting the Create post button. IT worked fine for spell check and preview. Thats the reason why the multiple posts.

Task summary is just a table which has structure,

Task date date
task name varchar2(50)

where i enter daily tasks so it might not have all the dates in a month.


But I want to have the sql output which shows me the Oracle week ('WW') , Day and Date so I know 01 Janury is "01" week.

thanks,
Rahul
Re: Get all days for a month, along with Week, Day when first, last day are passed [message #389377 is a reply to message #389375] Sun, 01 March 2009 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a Test case: create table and insert statements along with the result you want with these data.
And post why you think WW is not the week number you want. If b.e is correct then "to_char(b.e,'WW')" is correct in regard to its definition.

Regards
Michel
Re: Get all days for a month, along with Week, Day when first, last day are passed [message #389483 is a reply to message #389377] Mon, 02 March 2009 05:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm guessing (as you've not bothered to tell us) that you think the week is incorrect as it goes from Wednesday to Wednesday in the example you've posted.

If you check the details of the formatr mask you're using, you'll see what WW counts weeks frmo the first day of the year.
I suspect that you want the IW format mask instead.
Previous Topic: cursors in trigger
Next Topic: Trouble creating proc
Goto Forum:
  


Current Time: Fri Dec 02 20:51:17 CST 2016

Total time taken to generate the page: 0.18731 seconds