| [reply] |
How can I find start and end date for a given week and year?
First you need to settle on definitions. There are several conflicting definitions you have to address. Does your week start on a Sunday, or on a Monday? What defines the first week of the year (i.e., when does Week 1 start)? If I recall correctly (and it's been a while, so I might not), there's an ISO standard definition for numbering weeks that conflicts with standard business practice.
Get that settled for whatever problem you're dealing with, then generate a big lookup table.
| [reply] |
In addition to nailing down whether weeks begin on Sunday or Monday, there is also the issue of which week is the Nth week, dependant on when the company's year begins.
For example, with my previous company, the fiscal year ran from February through January (instead of January through December). To make things even more convoluted, the company followed the 4-5-4 calendar, so the "first" week of a given 'corporate' month may actually fall within the previous calendar month, or a few days into the calendar month (as defined by the way the rest of the Western world works). For example, the first week of April might actually start on Monday the 8th.
The reason for this kind of tinkering with the corporate calendar was so that it was easier to match up figures from business on a particular week this year, with business on a particular week last year. Leap years caused all sorts of havoc, and resulted in even more corporate tinkering with the calendar.
I bring this up to draw attention to the fact that to fit within a corporate culture, the software design may need to provide the flexibility for custom definition of the calendar. Certanly, within my previous company's corporate culture, any software that didn't allow for custom definition of the calendar would have been essentially worthless. Sales trends, open-to-buy (for buyers), shipping cutoff dates, profitability reporting, advertising schedules, etc., all relied upon the corporate calendar, which was, in fact, subject to change on occasion.
| [reply] |
There is probably a module on CPAN that will do something like this. But here is one easy way to do it utilizing mysql. Granted, this method probably is not by any means optimal considering the simple task at hand but it will give you the results you are looking for.
#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect('DBI:mysql:databasename','user','password') or
+die "Couldn't open database: ". DBI->errstr . "\n";
my $sth = $dbh->prepare("SELECT date_format(?,'%V')");
my $rc = $sth->execute('2003-01-5');
my @temp = $sth->fetchrow_array;
print "WEEK: $temp[0]\n";
exit;
By the way if your week starts on Monday instead of Sunday use this:
my $sth = $dbh->prepare("SELECT date_format(?,'%v')");
Again, let me say that this is not the way to do it but it will work. | [reply]
[d/l]
[select] |
Granted, this method probably is not by any means optimal considering the simple task at hand but it will give you the results you are looking for.
MySQL's date_format is a frontend to the POSIX function strftime. Use the Perl interface to this instead of MySQL.
use POSIX 'strftime';
# what week is December 1, 2003?
# strftime takes month-1 and year-1900 as arguments
print strftime("%U",0,0,0,1,11,103);
# or use "%V" for weeks starting Monday
| [reply]
[d/l] |
Thanks for the info. I didn't know about strftime. I'm sure I will find it useful in the future.
As I said in my original post, I know this is not the right way to do it but it will work. So I lost a little XP for posting a workable yet unworthy answer. At least I gained real experience from it and that's why I come to PM anyway (for experience and knowledge not points or trophies).
| [reply]
[d/l] |