Solved

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

Posted on 2010-09-20
15
979 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Python 2.7 - French characters 6 58
Vb script to unzip a files and rename the files 12 93
Autoit restart command not working 6 57
get remote physical servers and platforms 5 50
If you get a (Blue Screen of Death), your system writes a small file called a minidump. Your first step is to make certain your computer is setup to record memory dumps. Right click My Computer, choose properties. Click on the advanced tab, an…
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
The viewer will learn how to count occurrences of each item in an array.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

929 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

12 Experts available now in Live!

Get 1:1 Help Now