@@ -, +, @@ replace by CAST(now() AS date). --- C4/Acquisition.pm | 4 ++-- C4/NewsChannels.pm | 4 ++-- C4/Reserves.pm | 12 ++++++------ circ/overdue.pl | 2 +- misc/cronjobs/longoverdue.pl | 6 +++--- misc/cronjobs/stats/monthly_circulation_statistics.pl | 6 +++--- misc/cronjobs/stats/monthly_new_items_statistics.pl | 2 +- misc/cronjobs/stats/monthly_new_patron_statistics.pl | 2 +- 8 files changed, 19 insertions(+), 19 deletions(-) --- a/C4/Acquisition.pm +++ a/C4/Acquisition.pm @@ -2147,7 +2147,7 @@ sub GetContracts { $query = "SELECT * FROM aqcontract WHERE booksellerid=? - AND contractenddate >= CURDATE( )"; + AND contractenddate >= CAST(now() AS date)"; } my $sth = $dbh->prepare($query); $sth->execute( $booksellerid ); @@ -2203,7 +2203,7 @@ sub AddClaim { my $query = " UPDATE aqorders SET claims_count = claims_count + 1, - claimed_date = CURDATE() + claimed_date = CAST(now() AS date) WHERE ordernumber = ? "; my $sth = $dbh->prepare($query); --- a/C4/NewsChannels.pm +++ a/C4/NewsChannels.pm @@ -141,11 +141,11 @@ sub GetNewsToDisplay { SELECT *,timestamp AS newdate FROM opac_news WHERE ( - expirationdate >= CURRENT_DATE() + expirationdate >= CAST(now() AS date) OR expirationdate IS NULL OR expirationdate = '00-00-0000' ) - AND `timestamp` <= CURRENT_DATE() + AND `timestamp` <= CAST(now() AS date) AND lang = ? ORDER BY number "; # expirationdate field is NOT in ISO format? --- a/C4/Reserves.pm +++ a/C4/Reserves.pm @@ -277,7 +277,7 @@ sub GetReservesFromBiblionumber { FROM reserves WHERE biblionumber = ? "; unless ( $all_dates ) { - $query .= "AND reservedate <= CURRENT_DATE()"; + $query .= "AND reservedate <= CAST(now() AS date)"; } $query .= "ORDER BY priority"; my $sth = $dbh->prepare($query); @@ -343,7 +343,7 @@ sub GetReservesFromItemnumber { WHERE itemnumber=? "; unless ( $all_dates ) { - $query .= " AND reservedate <= CURRENT_DATE()"; + $query .= " AND reservedate <= CAST(now() AS date)"; } my $sth_res = $dbh->prepare($query); $sth_res->execute($itemnumber); @@ -897,7 +897,7 @@ sub CancelExpiredReserves { # Cancel reserves that have passed their expiration date. my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( " - SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() ) + SELECT * FROM reserves WHERE DATE(expirationdate) < CAST(now() AS date) AND expirationdate IS NOT NULL AND found IS NULL " ); @@ -1768,7 +1768,7 @@ sub _Findgroupreserve { AND priority > 0 AND item_level_request = 1 AND itemnumber = ? - AND reservedate <= CURRENT_DATE() + AND reservedate <= CAST(now() AS date) AND suspend = 0 /; my $sth = $dbh->prepare($item_level_target_query); @@ -1799,7 +1799,7 @@ sub _Findgroupreserve { AND priority > 0 AND item_level_request = 0 AND hold_fill_targets.itemnumber = ? - AND reservedate <= CURRENT_DATE() + AND reservedate <= CAST(now() as date) AND suspend = 0 /; $sth = $dbh->prepare($title_level_target_query); @@ -1831,7 +1831,7 @@ sub _Findgroupreserve { AND reserves.reservedate = reserveconstraints.reservedate ) OR reserves.constrainttype='a' ) AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?) - AND reserves.reservedate <= CURRENT_DATE() + AND reserves.reservedate <= CAST(now() AS date) AND suspend = 0 /; $sth = $dbh->prepare($query); --- a/circ/overdue.pl +++ a/circ/overdue.pl @@ -297,7 +297,7 @@ if ($noreport) { $strsth .= " AND borrowers.gonenoaddress <> 0"; } elsif ( $borflagsfilter eq 'debarred' ) { - $strsth .= " AND borrowers.debarred >= CURDATE()" ; + $strsth .= " AND borrowers.debarred >= CAST(now() AS date)" ; } elsif ( $borflagsfilter eq 'lost') { $strsth .= " AND borrowers.lost <> 0"; --- a/misc/cronjobs/longoverdue.pl +++ a/misc/cronjobs/longoverdue.pl @@ -117,7 +117,7 @@ unless ($confirm) { } # In my opinion, this line is safe SQL to have outside the API. --atz -our $bounds_sth = C4::Context->dbh->prepare("SELECT DATE_SUB(CURDATE(), INTERVAL ? DAY)"); +our $bounds_sth = C4::Context->dbh->prepare("SELECT DATE_SUB(CAST(now() AS date), INTERVAL ? DAY)"); sub bounds ($) { $bounds_sth->execute(shift); @@ -130,8 +130,8 @@ sub longoverdue_sth { SELECT items.itemnumber, borrowernumber, date_due FROM issues, items WHERE items.itemnumber = issues.itemnumber - AND DATE_SUB(CURDATE(), INTERVAL ? DAY) > date_due - AND DATE_SUB(CURDATE(), INTERVAL ? DAY) <= date_due + AND DATE_SUB(CAST(now() AS date), INTERVAL ? DAY) > date_due + AND DATE_SUB(CAST(now() AS date), INTERVAL ? DAY) <= date_due AND itemlost <> ? ORDER BY date_due "; --- a/misc/cronjobs/stats/monthly_circulation_statistics.pl +++ a/misc/cronjobs/stats/monthly_circulation_statistics.pl @@ -64,12 +64,12 @@ my $dbh = C4::Context->dbh; my $sth2 = $dbh->prepare ("SELECT branchcode, branchname FROM branches ORDER BY branchcode"); # number of checkouts for this library -my $sth3 = $dbh->prepare ("SELECT COUNT(*) FROM biblioitems,items,statistics WHERE biblioitems.biblioitemnumber=items.biblioitemnumber AND statistics.itemnumber=items.itemnumber AND items.ccode=? AND YEAR(statistics.datetime)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND MONTH(statistics.datetime)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND statistics.branch=? AND statistics.type='issue' GROUP BY ccode"); +my $sth3 = $dbh->prepare ("SELECT COUNT(*) FROM biblioitems,items,statistics WHERE biblioitems.biblioitemnumber=items.biblioitemnumber AND statistics.itemnumber=items.itemnumber AND items.ccode=? AND YEAR(statistics.datetime)=YEAR(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) AND MONTH(statistics.datetime)=MONTH(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) AND statistics.branch=? AND statistics.type='issue' GROUP BY ccode"); # number of renewals for this library my $sth4 = $dbh->prepare ("SELECT COUNT(statistics.itemnumber) FROM statistics,items,biblioitems - WHERE YEAR(statistics.datetime)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) - AND MONTH(statistics.datetime)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) + WHERE YEAR(statistics.datetime)=YEAR(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) + AND MONTH(statistics.datetime)=MONTH(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) AND statistics.itemnumber=items.itemnumber AND biblioitems.ccode=? AND homebranch=? --- a/misc/cronjobs/stats/monthly_new_items_statistics.pl +++ a/misc/cronjobs/stats/monthly_new_items_statistics.pl @@ -59,7 +59,7 @@ unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'localhost'; my $dbh = C4::Context->dbh; -my $sth = $dbh->prepare ("SELECT biblioitems.ccode,COUNT(biblioitems.ccode) FROM items,biblioitems WHERE YEAR(items.dateaccessioned)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND MONTH(items.dateaccessioned)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND biblioitems.biblioitemnumber=items.biblioitemnumber GROUP BY biblioitems.ccode"); +my $sth = $dbh->prepare ("SELECT biblioitems.ccode,COUNT(biblioitems.ccode) FROM items,biblioitems WHERE YEAR(items.dateaccessioned)=YEAR(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) AND MONTH(items.dateaccessioned)=MONTH(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) AND biblioitems.biblioitemnumber=items.biblioitemnumber GROUP BY biblioitems.ccode"); my ($row,$itemtype,$count); --- a/misc/cronjobs/stats/monthly_new_patron_statistics.pl +++ a/misc/cronjobs/stats/monthly_new_patron_statistics.pl @@ -60,7 +60,7 @@ unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'localhost'; my $dbh = C4::Context->dbh; my $sth1 = $dbh->prepare ("SELECT categorycode FROM categories ORDER BY categorycode"); -my $sth2 = $dbh->prepare ("SELECT branchcode,COUNT(branchcode) FROM borrowers WHERE categorycode=? AND YEAR(dateenrolled)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND MONTH(dateenrolled)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) GROUP BY branchcode"); +my $sth2 = $dbh->prepare ("SELECT branchcode,COUNT(branchcode) FROM borrowers WHERE categorycode=? AND YEAR(dateenrolled)=YEAR(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) AND MONTH(dateenrolled)=MONTH(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) GROUP BY branchcode"); my ($rowc,$rowb,$categorycode,$branchcode,$count,$line); --