Solved

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

Posted on 2010-09-20
15
978 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
  • 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 500 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Making a simple AJAX shopping cart Couple years ago I made my first shopping cart, I used iframe and JavaScript, it was very good at that time, there were no sessions or AJAX, I used cookies on clients machine. Today we have more advanced techno…
It is becoming increasingly popular to have a front-page slider on a web site. Nearly every TV website,  magazine or online news has one on their site, and even some e-commerce sites have one. Today you can use sliders with Joomla, WordPress or …
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now