• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 998
  • Last Modified:

Check to see if date is greater than 6 months from today

I need to pull data from a mysql database, display some code based on that data and then IF the date pulled is greater than 6 months ago from today, display some more code.  Is there a way to do this in the mysql query (cannot do as a where clause, because I need the rest of the data even if the date is not greater than 6 months ago), or do I have to use the perl date module?

sample code ---- cgi source
my $sql = "SELECT pmts.pmtID, pmts.payStatus, pmts.payDate, pmts.closed, pmts.programType, DATE_FORMAT(pmts.closeDate,'%Y-%m-%d') as closeDate, ba.paid, DATE_FORMAT(ba.paidDate,'%Y-%m-%d') as paidDate, sig.financeType, pt.typeTitle, v.done, v.dateDone from tbl_pmts as pmts LEFT OUTER join broker_accounting as ba on ba.pmtID=pmts.pmtID inner join tbl_contract_sigs as sig on sig.i_contact=pmts.i_contact inner join tb_pay_type as pt on pt.financeType=sig.financeType left outer join tbl_client_verification as v on v.i_contact=pmts.i_contact where pmts.i_contact='$m_i_contact' order by payDate DESC; ";
            ifsDbConnect();
            my $sth = $dbh->prepare($sql);
            $sth->execute;
            my $cRows = $sth->rows;
            my $closed = 0;
            my $closeDate;
            if ( $cRows > 0 )
            {
 my $rst = $sth->fetchrow_hashref();
                  my $cust = $rst->{'payStatus'};
                  my $cust_date = $rst->{'payDate'};
                  my $cust_status = '';
                  my $progType = $rst->{'programType'};
                  my $contract = $rst->{'typeTitle'};
                  my $done = $rst->{'done'};
                  my $doneDate = $rst->{'dateDone'};
                  my $bPaid = $rst->{'paid'};
                  my $bPaidDate = $rst->{'paidDate'};
                  my $pmtID = $rst->{'pmtID'};
                  $closed = $rst->{'closed'};
                  $closeDate = $rst->{'closeDate'};
                  if ($cust eq 'R') {
                        $cust_status='Payment Requested';
                  }
                  if ($cust eq 'P') {
                        $cust_status = 'Paid';
                  }
### here want to add if $closeDate>6months ago -- or, if can include as if statement in MYSQL query then if $flag==true...
}
0
thewebwench
Asked:
thewebwench
  • 6
  • 5
  • 4
1 Solution
 
FishMongerCommented:
I'm not an expert in mysql queries, but since you want to retrieve the data irrespective of the date, you'll be needing to do the date calc in Perl.

Take a look at the Delta_Days function in Date::Calc
http://search.cpan.org/~stbey/Date-Calc-6.3/lib/Date/Calc.pod
0
 
thewebwenchAuthor Commented:
it looks like I don't have access to Date::Calc

[Mon Sep 20 18:25:49 2010] [error] [client 65.40.11.140] Can't locate Date/Calc.pm in @INC (@INC contains: /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.7/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl/5.8.7 /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.7/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl/5.8.7 /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.8/i386-linux-thread-multi /usr/lib/perl5/5.8.8 .) at broker_cust_profile.cgi line 13603.
0
 
FishMongerCommented:
You need to install the module.  If you don't have root access, you still can install it into any location that you have write access.

This article talks about installing mod_perl without root access, but it's the same process for any module.
http://www.perl.com/pub/2002/04/10/mod_perl.html
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
FishMongerCommented:
Here's another walk-through which is more concise.
http://www.perlmonks.org/index.pl?node_id=128077#permission
0
 
thewebwenchAuthor Commented:
I don't have shell access either
0
 
FishMongerCommented:
Do you have a cpanel that lets you install modules?  If not, then contact your host and ask them to install the module.  If neither of those options work, then it's time to find another host.
0
 
thewebwenchAuthor Commented:
checking into that now
0
 
jeromeeCommented:
you could write a poorman version using POSIX::strptime to parse the mysql date into a number of seconds, then subtract 6 month-worth of second out of that number, you can then convert it to a ccyymmdd date which is easy to compare (gt or even >) to any date.

Let me know if you need more explanations.
0
 
thewebwenchAuthor Commented:
I need more explanations
0
 
jeromeeCommented:
Please review the attached code.
It might not be exactly what you want but it should demonstrate the idea.

Good luck!
use strict;

# get the ccyymmdd date from 6 months ago
my $sixMonthsAgoSeconds = time - (60*60*24*(365.25/2));  # Adjust this if needed
my($day, $mon, $year) = (localtime($sixMonthsAgoSeconds))[3..5];
my $sixMonthagoCCyymmdd = sprintf("%d-%02d-%02d", $year+1900, $mon+1, $day);

my $ccyymmddFromDatabase = "2010-07-27"; # assuming that this is value that you got from your database with a format like "ccyy-mm-dd"

print "$ccyymmddFromDatabase is older than 6 months\n" if $sixMonthagoCCyymmdd gt $ccyymmddFromDatabase;

Open in new window

0
 
thewebwenchAuthor Commented:
I now have the calc module installed.  I want to reward jeromee for his workaround, but still need help with using the calc module to get the date difference (just looking for a true/false of is>6months)
0
 
jeromeeCommented:
Here's the solution using Date::Calc

Good luck!
use strict;
use Date::Calc qw(Add_Delta_YM);

my ($ccyy6Mo, $mm6Mo, $dd6Mo) = Add_Delta_YM((localtime)[5,4,3], 0, 6);	# date 6 months ago
my $sixMonthagoCCyymmdd = sprintf("%d-%02d-%02d", $ccyy6Mo+1900, $mm6Mo+1, $dd6Mo);

my $ccyymmddFromDatabase = "2010-01-27"; # assuming that this is value that you got from your database with a format like "ccyy-mm-dd"

print "$ccyymmddFromDatabase is older than 6 months ($sixMonthagoCCyymmdd) \n" if $sixMonthagoCCyymmdd gt $ccyymmddFromDatabase;

Open in new window

0
 
jeromeeCommented:
Small but ESSENTIAL typo...


se strict;
use Date::Calc qw(Add_Delta_YM);

my ($ccyy6Mo, $mm6Mo, $dd6Mo) = Add_Delta_YM((localtime)[5,4,3], 0, -6);	# date 6 months ago (subtract 6)
my $sixMonthagoCCyymmdd = sprintf("%d-%02d-%02d", $ccyy6Mo+1900, $mm6Mo+1, $dd6Mo);

my $ccyymmddFromDatabase = "2010-01-27"; # assuming that this is value that you got from your database with a format like "ccyy-mm-dd"

print "$ccyymmddFromDatabase is older than 6 months ($sixMonthagoCCyymmdd) \n" if $sixMonthagoCCyymmdd gt $ccyymmddFromDatabase;

Open in new window

0
 
thewebwenchAuthor Commented:
found that -- thx
0
 
jeromeeCommented:
Glad to hear I was able to help!
Happy Perling!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now