OraFAQ Forum: SQL & PL/SQL » Date Functions

Home » SQL & PL/SQL » SQL & PL/SQL » Date Functions

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

Date Functions [message #244845]

Thu, 14 June 2007 05:42

ziggy25
Messages: 206
Registered: July 2005

Senior Member

Hi All,

I've changed this slightly and would like your opinion on it. Here is what i am trying to achieve.

- A weekly report to be produced every Sunday.
- The start date is the first day of the week
- The end date is the last day of the week
- The start date cannot be a day in the previous month. For example if the Sunday falls on the 3rd day of the week (e.g. 3rd June 07), the start date will be the first day of the month.
- The End date cannot be a day in the next month. For example, if the Sunday falls on 2nd day of the next month (e.g. 24th June 07), the end date will be the last day of the current month (i.e. 30th of June).

This is what i've come up with and would like your opinion or comments on whether you think i might have missed some (orphaned) days or whether the method i chose might be wrong.

Thanks in advance.

-- Check if its Sunday or last day of month
IF To_Char(SYSDATE, 'DAY')='SUNDAY' OR (TRUNC(sysdate) = TRUNC(LAST_DAY(sysdate))) THEN
  -- Get Start date of report
  -- Check that the start date is not on the previous month
  IF (To_Char(SYSDATE, 'DAY')='SUNDAY') AND TRUNC((SYSDATE)-6),'MM')=TRUNC(SYSDATE,'MM') THEN
    -- not gone to previous month
    lv_start_date:=TO_DATE(SYSDATE)-6);
  ELSE
    -- gone to previous month so just use first day of the month
    lv_start_date:=TRUNC(SYSDATE,'MONTH');
  END IF;

  -- Get end date of report
  -- Check that the end date is not on the next month
  IF (To_Char(SYSDATE, 'DAY')='SUNDAY') AND (TRUNC(sysdate) <= TRUNC(LAST_DAY(sysdate))) THEN
    -- not gone to next month
    lv_end_date:=SYSDATE;
  ELSE
    -- gone to next month so just use last day of the month
    lv_end_date:=TRUNC(LAST_DAY(sysdate));
  END IF;

  -- Run report
  stats_package.producestats(lv_start_date,lv_end_Date);
END IF;

Report message to a moderator

Re: Date Functions [message #244873 is a reply to message #244845]

Thu, 14 June 2007 07:17

techno
Messages: 44
Registered: October 2003

Member

You can use the following

if to_char(sysdate,'DAY') = 'SUNDAY' then
 lv_start_date := greatest(trunc(sysdate,'MONTH'), next_day(trunc(sysdate)-1,'SUNDAY'));
 lv_end_Date   := least(last_day(trunc(sysdate)), next_day(trunc(sysdate),'SATURDAY'));
-- Run report
  stats_package.producestats(lv_start_date,lv_end_Date);
end if;

Regards
Techno

Report message to a moderator

Re: Date Functions [message #244877 is a reply to message #244873]

Thu, 14 June 2007 07:26

ziggy25
Messages: 206
Registered: July 2005

Senior Member

techno wrote on Thu, 14 June 2007 07:17

You can use the following

if to_char(sysdate,'DAY') = 'SUNDAY' then
 lv_start_date := greatest(trunc(sysdate,'MONTH'), next_day(trunc(sysdate)-1,'SUNDAY'));
 lv_end_Date   := least(last_day(trunc(sysdate)), next_day(trunc(sysdate),'SATURDAY'));
-- Run report
  stats_package.producestats(lv_start_date,lv_end_Date);
end if;

Regards
Techno

Thanks for your response. Could you explain exactly what its doing.

Thanks

Report message to a moderator

Re: Date Functions [message #244879 is a reply to message #244845]

Thu, 14 June 2007 07:44

ziggy25
Messages: 206
Registered: July 2005

Senior Member

Hi thanks for your response.

I tried the above with SUnday 17th June being the current date and this is what i got.

select trunc(TO_DATE('17JUN07'),'MONTH'), next_day(trunc(TO_DATE('17JUN07'))-1,'SUNDAY') from dual;
select last_day(trunc(TO_DATE('17JUN07'))), next_day(trunc(TO_DATE('17JUN07')),'SATURDAY') from dual;

TRUNC(TO_DATE('17JUN07'),'MONTH') NEXT_DAY(TRUNC(TO_DATE('17JUN07'))-1,'SUNDAY') 
------------------------- ------------------------- 
01-JUN-07                 17-JUN-07                 

1 rows selected

LAST_DAY(TRUNC(TO_DATE('17JUN07'))) NEXT_DAY(TRUNC(TO_DATE('17JUN07')),'SATURDAY') 
------------------------- ------------------------- 
30-JUN-07                 23-JUN-07

lv_start_date will be 17th June
lv_end_date will be 23rd June

This will be wrong because if we are on 17th of June, the end date should be 17th of June and start date should be 11th of June.

Thanks

Report message to a moderator

Re: Date Functions [message #244889 is a reply to message #244879]

Thu, 14 June 2007 08:36

techno
Messages: 44
Registered: October 2003

Member

I need clarification

Let the date is 03-JUL and the day is SUNDAY please explain what should be your starting date and ending date for this case

Techno

Report message to a moderator

Re: Date Functions [message #244910 is a reply to message #244889]

Thu, 14 June 2007 10:12

ziggy25
Messages: 206
Registered: July 2005

Senior Member

techno wrote on Thu, 14 June 2007 08:36

I need clarification

Let the date is 03-JUL and the day is SUNDAY please explain what should be your starting date and ending date for this case

Techno

If its on a Sunday, the start date should be the first day of that week (i.e. monday). The end date should be the Sunday.

Here are a few examples (for year 2007)

                       startDate  endDate
SUNDAY   08JUL07       02JUL07    08JUL07
THURSDAY 31JUL07       30JUL07    31JUL07 (Next Sunday is next month so we use last day of the month)
SUNDAY   05AUG07       01AUG07    05AUG07
SUNDAY   02SEP07       01SEP07    02SEP07
WEDNESDAY31OCT07       29OCT07    31OCT07
SUNDAY   01APR07       26MAR07    31MAR07

- The end of the week is always Sunday unless its the last day of the month.
- the start date of the week is always Monday unless the first day of the month falls on a Non Monday.

thanks

[Updated on: Thu, 14 June 2007 11:57]

Report message to a moderator

Previous Topic:	PACKED data conversion
Next Topic:	Is it possible to execute a procedure in next_date attribute of dbms_job

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 06:41:05 CST 2024