?
Solved

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

Posted on 2010-09-20
15
Medium Priority
?
995 Views
Last Modified: 2013-12-25
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
Comment
Question by:thewebwench
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
15 Comments
 
LVL 28

Expert Comment

by:FishMonger
ID: 33720829
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
 

Author Comment

by:thewebwench
ID: 33720982
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
 
LVL 28

Expert Comment

by:FishMonger
ID: 33721068
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:FishMonger
ID: 33721104
Here's another walk-through which is more concise.
http://www.perlmonks.org/index.pl?node_id=128077#permission
0
 

Author Comment

by:thewebwench
ID: 33721200
I don't have shell access either
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 33721274
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
 

Author Comment

by:thewebwench
ID: 33721384
checking into that now
0
 
LVL 10

Expert Comment

by:jeromee
ID: 33721453
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
 

Author Comment

by:thewebwench
ID: 33724650
I need more explanations
0
 
LVL 10

Expert Comment

by:jeromee
ID: 33725599
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
 

Author Comment

by:thewebwench
ID: 33737152
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
 
LVL 10

Expert Comment

by:jeromee
ID: 33739741
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
 
LVL 10

Accepted Solution

by:
jeromee earned 2000 total points
ID: 33739769
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
 

Author Comment

by:thewebwench
ID: 33747857
found that -- thx
0
 
LVL 10

Expert Comment

by:jeromee
ID: 33748220
Glad to hear I was able to help!
Happy Perling!
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to make a simple HTML bar chart with the usage of WhizBase, If you want more information about WhizBase please read my previous articles at http://www.experts-exchange.com/ARTH_5123186.html (http://www.experts-ex…
It is a general practice to get rid of old user profiles on a computer  in a LAN environment. As I have been working with a company in a LAN environment where users move from one place to some other place at times. This will make many user profil…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question