Home » SQL & PL/SQL » SQL & PL/SQL » Number weeks between two dates which are in same year or different year. (merged)
Number weeks between two dates which are in same year or different year. (merged) [message #415972] Thu, 30 July 2009 00:35 Go to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
Hi,

I am facing problem when finding out no of weeks between two dates when they are in different year.
For example if Date1=30-jul-2009 and Date2=2-feb-2010 then how do I find out the no of weeks between those two dates? If both date are in the same year then we can find out the no of weeks by subtracting the corresponding week no of the two given dates.But I really require help two finding no of weeks between two dates which are in different year.Please suggest any solution....

Regards,

Subhadip
Re: Number weeks between two dates which are in same year or different year. [message #415978 is a reply to message #415972] Thu, 30 July 2009 00:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Please post problem 3rd time to be sure I understand it.
Re: Number weeks between two dates which are in same year or different year. [message #415980 is a reply to message #415972] Thu, 30 July 2009 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define "number of weeks".
What should be the result for the dates you gave? Why?

Regards
Michel
Re: Number weeks between two dates which are in same year or different year. [message #415985 is a reply to message #415980] Thu, 30 July 2009 01:01 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
Result will be 27.Coz, 30-jul-2009 is the 31st week of the year 2009 and 02-feb-2010 is the 5th week of the year 2010.

So the remaining week in year 2009 is 22(inclusive of current week) and in 2010 the no week upto 02-feb-2010 is 5.So the number of week should be 27.

[Updated on: Thu, 30 July 2009 01:02]

Report message to a moderator

Re: Number weeks between two dates which are in same year or different year. [message #415992 is a reply to message #415985] Thu, 30 July 2009 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you have the algorithm what is your problem putting in SQL?

Note: format element "WW" gives the week number of a date.

Regards
Michel
Re: Number weeks between two dates which are in same year or different year. [message #415993 is a reply to message #415992] Thu, 30 July 2009 01:40 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
I want to use IW as format.
Re: Number weeks between two dates which are in same year or different year. [message #415995 is a reply to message #415985] Thu, 30 July 2009 02:01 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

create a table for one perticular Day e.g. sat .
Populate all saturdays for the given year and then it becomes easy to get no of weeks for the given time period.

Chk if it works for you
Attaching the code


CREATE OR REPLACE PROCEDURE sp_ins_holiday_list (
v_year VARCHAR2 DEFAULT TO_CHAR (SYSDATE, 'YYYY')
)
AS
v_first_sat DATE;
v_last_date DATE;
v_first_date DATE;
v_sat DATE;
v_sun DATE;
BEGIN
v_last_date := TO_DATE (('31/12/' || v_year), 'DD/MM/YYYY');
v_first_date := TO_DATE (('01/01/' || v_year), 'DD/MM/YYYY');

SELECT NEXT_DAY (TRUNC (v_first_date, 'MM') - 1, 'sat')
INTO v_first_sat
FROM DUAL;

v_sat := v_first_sat;

WHILE v_sat <= v_last_date
LOOP
MERGE INTO holiday_calender a
USING (SELECT v_sat holiday_dt
FROM DUAL) b
ON (b.holiday_dt = a.holiday_dt)
WHEN MATCHED THEN
UPDATE
SET modified_by = USER, mofdified_dt = SYSDATE
WHEN NOT MATCHED THEN
INSERT (holiday_dt, holiday_desc, created_by)
VALUES (b.holiday_dt, 'WEEKLY OFF: SATURDAY', USER);
v_sat := v_sat + 7;
END LOOP;

COMMIT;
END;



Re: Number weeks between two dates which are in same year or different year. [message #415997 is a reply to message #415993] Thu, 30 July 2009 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
subhadip.chanda wrote on Thu, 30 July 2009 08:40
I want to use IW as format.

So use it.

Regards
Michel
Re: Number weeks between two dates which are in same year or different year. [message #416000 is a reply to message #415995] Thu, 30 July 2009 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Prajakta001

From your previous post:
Michel Cadot wrote on Wed, 29 July 2009 14:53
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...

PLEASE DO IT.

In addition, why using PL/SQL and a table with Saturday when you can do it in SQL? Are you searching for the most complicated way to do it?

Regards
Michel

Re: Number weeks between two dates which are in same year or different year. [message #416016 is a reply to message #416000] Thu, 30 July 2009 03:17 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
How to proceed using SQL.Please suggest...
Re: Number weeks between two dates which are in same year or different year. [message #416017 is a reply to message #416016] Thu, 30 July 2009 03:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Type it into the SQL prompt and hit enter?
Re: Number weeks between two dates which are in same year or different year. [message #416020 is a reply to message #416016] Thu, 30 July 2009 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
subhadip.chanda wrote on Thu, 30 July 2009 10:17
How to proceed using SQL.Please suggest...

As you said (pseudo-code):
case when week2 > week1 then week2-week1 else "last week of year"-week1+week2

Regards
Michel
Re: Number weeks between two dates which are in same year or different year. [message #416047 is a reply to message #416020] Thu, 30 July 2009 04:57 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
What happens if the dates are Date1=30-jul-2009 and Date2=2-feb-2011?
Maybe I'm being really obtuse here, but would it not be easier to simply divide the number of days between the two dates by 7? (Obviously including a little bit of cleanup too, don't want to give the whole game away Wink)
Re: Number weeks between two dates which are in same year or different year. [message #416078 is a reply to message #416047] Thu, 30 July 2009 06:48 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
 select (to_date('2-feb-2010','dd-mon-yyyy')-to_date('30-jul-2009','dd-mon-yyyy'))/7 from dual

this is enough help Smile
Re: Number weeks between two dates which are in same year or different year. [message #416080 is a reply to message #416078] Thu, 30 July 2009 07:03 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
ayush_anand wrote on Thu, 30 July 2009 12:48
 select (to_date('2-feb-2010','dd-mon-yyyy')-to_date('30-jul-2009','dd-mon-yyyy'))/7 from dual

this is enough help Smile


I disagree, I think that
Quote:
divide the number of days between the two dates by 7
was enough help.
Re: Number weeks between two dates which are in same year or different year. (merged) [message #416133 is a reply to message #415972] Thu, 30 July 2009 13:31 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
one more think. how many weeks would it be if it was 7.5 weeks use one of the following

select floor((to_date('2-feb-2010','dd-mon-yyyy')-to_date('30-jul-2009','dd-mon-yyyy'))/7) from dual

or

select ceil(to_date('2-feb-2010','dd-mon-yyyy')-to_date('30-jul-2009','dd-mon-yyyy'))/7) from dual

Re: Number weeks between two dates which are in same year or different year. [message #416168 is a reply to message #416080] Thu, 30 July 2009 23:51 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

oh thats quite simple answer .. Guess made it cmplicated ..actually had implemented above code in one the logics ehere they want to include holidays as weekends Smile

Cheers
Prajakta
Re: Number weeks between two dates which are in same year or different year. (merged) [message #416231 is a reply to message #416133] Fri, 31 July 2009 05:13 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
No it shouldnot be the way I want.I want the no of week as per the calender.and my week start from monday and end at Sunday.Now
if Date 1 is 31-jul-2009 and date2 is 3-Aug-09,then no of week should be 2.But if I finding the no of date between date1 and date2 means (date1-date2)/7 or floor((date1-date2)/7) I will not get right answer. please suggest How to proceed for solving this...
Re: Number weeks between two dates which are in same year or different year. (merged) [message #416239 is a reply to message #416231] Fri, 31 July 2009 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
please suggest How to proceed for solving this...

Just read what has been posted. The answer is there.

Regards
Michel
Re: Number weeks between two dates which are in same year or different year. (merged) [message #416260 is a reply to message #416239] Fri, 31 July 2009 07:13 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
No my problem is not yet solved.

Date1:='3-aug-2009';
Date2:='31-jul-2009';
then
select ceil((to_date('3-aug-2009','dd/mm/yyyy')-to_date('31-jul-2009','dd/mm/yyyy'))/7) from dual;
will give output as 1.

But '31-jul-2009' is this week.and '3-aug-2009' is lying in next week.So the number week is 2.
Re: Number weeks between two dates which are in same year or different year. (merged) [message #416274 is a reply to message #416260] Fri, 31 July 2009 07:36 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
select start_date.d_a_t_e -  end_date.d_a_t_e+1
from
(
select to_char(to_date('3-aug-2009','dd/mm/yyyy')-(SELECT (NEXT_DAY('01-JAN-2009','SUNDAY') - to_date('1-jan-2009','dd/mm/yyyy')) offset    FROM DUAL),'ww') d_a_t_e from dual
) start_date,
(
select to_char(to_date('31-jul-2009','dd/mm/yyyy')-(SELECT (NEXT_DAY('01-JAN-2009','SUNDAY') - to_date('1-jan-2009','dd/mm/yyyy')) offset    FROM DUAL),'ww') d_a_t_e from dual
) end_date


try to understand this
hope this gives you some idea of one of the ways of solving your problem
this does not works for dates in different years

[Updated on: Fri, 31 July 2009 07:42]

Report message to a moderator

Re: Number weeks between two dates which are in same year or different year. (merged) [message #416278 is a reply to message #416260] Fri, 31 July 2009 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
subhadip.chanda wrote on Fri, 31 July 2009 14:13
No my problem is not yet solved.

Date1:='3-aug-2009';
Date2:='31-jul-2009';
then
select ceil((to_date('3-aug-2009','dd/mm/yyyy')-to_date('31-jul-2009','dd/mm/yyyy'))/7) from dual;
will give output as 1.

But '31-jul-2009' is this week.and '3-aug-2009' is lying in next week.So the number week is 2.

What about:
Michel Cadot wrote on Thu, 30 July 2009 10:28
subhadip.chanda wrote on Thu, 30 July 2009 10:17
How to proceed using SQL.Please suggest...

As you said (pseudo-code):
case when week2 > week1 then week2-week1 else "last week of year"-week1+week2

Regards
Michel



Re: Number weeks between two dates which are in same year or different year. (merged) [message #416282 is a reply to message #416260] Fri, 31 July 2009 08:05 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Date1:='3-aug-2009';
Date2:='31-jul-2009';
then
select ceil((to_date('3-aug-2009','dd/mm/yyyy')-to_date('31-jul-2009','dd/mm/yyyy'))/7) from dual;
will give output as 1.


if this is your problem then add 1 Smile
as 2-1 is always 1 and not 2

[Updated on: Fri, 31 July 2009 08:06]

Report message to a moderator

Previous Topic: display
Next Topic: Join two SQL statements
Goto Forum:
  


Current Time: Thu Dec 08 16:03:24 CST 2016

Total time taken to generate the page: 0.10044 seconds