I'm using Doctrine for the ORM of my application. In a table I have a 'start_date'.
In php, I can calculate 3 days before a date:
date('l jS F (Y-m-d)', strtotime('-3 days'));
But this includes weekends, I would like to only include business days.
I.e. If an event was on a Wednesday, I would get a reminder email on the Sunday. I would prefer to get the reminder email on a friday instead. So how would I calculate to only use Mon-Fri for calculating?
If you know how to do it in Oracle too, that would be awesome.
I would prefer to have it in php so I don't have to use a custom oracle function (in case I wish to change the database engine) however it would be useful to know in oracle too :-)
Thanks guys.
PHP 5.3 supports relative dates like weekdays, e.g.
echo date('Y-m-d', strtotime('-3 weekdays', strtotime('2010-12-08')));
will produce 2010-12-03 (Friday) as opposed to
echo date('Y-m-d', strtotime('-3 days', strtotime('2010-12-08')));
which will produce 2010-12-05 (Sunday)
Any formats supported by strtotime are also supported in the DateTime API.
Afaik, it takes some gymnastics to do this calculation from within Oracle
date('w') gives you a numerical representation of the day of the week. 0 (sunday) to 6 (saturtay) you could just check if your date is 0 or 6 and then substract 2 (or one) additional days.
$n = 3
$timestamp = strtotime("-$n days")
$day = date('w', $timestamp);
if ($day == 0) {
$n = $n - 2;
} else if ($day == 6) {
$n = $n - 1;
}
$timestamp = strtotime("-$n days")
$dateString = date('l jS F (Y-m-d)', $timestamp);
Sorry, I don't know any oracle specific code.
In Oracle, to get the day of the week (1-7), use the following
select to_char(sysdate, 'D')
from dual
Which makes it trivial to exclude Saturdays and Sundays.
The exact definition of "business days", however, is likely to vary greatly from location to location (and business to business), so in that case I would suggest a database table to hold the specific business dates (as actual dates).