Generate Start - End date [message #638751] |
Fri, 19 June 2015 11:37 |
|
lott42_gmail
Messages: 146 Registered: June 2010
|
Senior Member |
|
|
I'm trying to find a start date and an end date starting from a Friday at 6:30 PM to the next Friday at 6:00 PM.
If the current date is on a Friday and the time is < 6:00pm than I want to look at the previous 7 days(Friday to Friday) else look at the next 7 days(Friday to Friday). I only need a start and end date.
so if a given date happens to be
06/19/2015 11:00 AM (Friday) I want a Start_Date of 6/12/2015 6:30:00 PM and an End_Date of 6/19/2015 6:00:00 PM
06/19/2015 7:00 PM (Friday) I want a Start_Date of 6/19/2015 6:30:00 PM and an End_Date of 6/26/2015 6:00:00 PM
06/20/2015 7:00 PM (Saturday) I want a Start_Date of 6/19/2015 6:30:00 PM and an End_Date of 6/26/2015 6:00:00 PM
I came up with this sql but it seems like their might be an easier way
WITH current_date_info AS
(SELECT to_date('6/19/2015 5:00 PM', 'mm/dd/yyyy hh:mi PM') test_day
FROM dual),
test_date_info AS
(SELECT CASE
WHEN to_char(test_day, 'DY') = 'FRI' AND
to_char(test_day, 'hh24mi') < '1800' THEN
test_day - 1
ELSE
test_day
END target_day
FROM current_date_info)
SELECT to_date(to_char(next_day(trunc(target_day) - 7, 'FRI'), 'mm/dd/yyyy') ||
' 6:30 PM',
'mm/dd/yyyy hh:mi AM') start_date,
to_date(to_char(next_day(trunc(target_day), 'FRI'), 'mm/dd/yyyy') ||
' 6:00 PM',
'mm/dd/yyyy hh:mi AM') end_date
FROM test_date_info;
Result
START_DATE END_DATE
6/12/2015 6:30:00 PM 6/19/2015 6:00:00 PM
|
|
|
|
Re: Generate Start - End date [message #638758 is a reply to message #638757] |
Fri, 19 June 2015 13:42 |
|
lott42_gmail
Messages: 146 Registered: June 2010
|
Senior Member |
|
|
Quote:
at what point in the week does the current time switch from being after the cutoff time to being before the cutoff time?
Only on Friday. If the date is after 6:00 on Friday, use "current_date" and use "current" Friday to the following Friday but if the time is before 6:00 on Friday we want to look at previous week Friday to the "current" Friday. If for ex., "today" was "Friday on 06/19/2015 5pm" I want a start date from 6/12/2015 6:30:00 PM to 6/19/2015 6:00:00 PM and if "today" was "Friday on 06/19/2015 7pm" I want a start date from 6/19/2015 6:30:00 PM to 6/26/2015 6:00:00 PM
Quote:
Sunday 5PM
Sunday 7PM
Monday 5PM
Monday 7PM
Tuesday 5PM
Tuesday 7PM
Wednesday 5PM
Wednesday 7PM
Thursday 5PM
Thursday 7PM
Don't really have a bearing on this. If for ex., "today" was "Thursday on 06/18/2015" I want a start date from 6/12/2015 6:30:00 PM to 6/19/2015 6:00:00 PM
This sql would work for these "type" dates but NOT on Friday at a certain time. I'm assuming Thursday 6/18/2015
SELECT to_date(to_char(next_day(trunc(SYSDATE - 1) - 7, 'FRI'),
'mm/dd/yyyy') || ' 6:30 PM',
'mm/dd/yyyy hh:mi AM') start_date,
to_date(to_char(next_day(trunc(SYSDATE - 1), 'FRI'), 'mm/dd/yyyy') ||
' 6:00 PM',
'mm/dd/yyyy hh:mi AM') end_date
FROM dual;
|
|
|