=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramInstanceStore.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramInstanceStore.java	2013-11-26 13:57:43 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramInstanceStore.java	2013-12-02 03:34:33 +0000
@@ -294,8 +294,8 @@
     {
         return "SELECT pi.programinstanceid, p.phonenumber, prm.templatemessage, "
             + "         p.name, org.name as orgunitName, " + "         pg.name as programName, pi.dateofincident , "
-            + "         pi.enrollmentdate,(now() - pi.enrollmentdate ) as days_since_erollment_date, "
-            + "         (now() - pi.dateofincident) as days_since_incident_date "
+            + "         pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, "
+            + "         (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date "
             + "       FROM patient p INNER JOIN programinstance pi "
             + "              ON p.patientid=pi.patientid INNER JOIN program pg "
             + "              ON pg.programid=pi.programid INNER JOIN organisationunit org "
@@ -303,8 +303,8 @@
             + "              ON prm.programid = pi.programid " + "       WHERE pi.status= "
             + ProgramInstance.STATUS_ACTIVE + "         and p.phonenumber is not NULL and p.phonenumber != ''   "
             + "         and prm.templatemessage is not NULL and prm.templatemessage != ''   "
-            + "         and pg.type=1 and prm.daysallowedsendmessage is not null  " + "         and ( now() - pi."
-            + dateToCompare + " ) = prm.daysallowedsendmessage "
+            + "         and pg.type=1 and prm.daysallowedsendmessage is not null    "
+            + "         and ( DATE(now()) - DATE(pi." + dateToCompare + ") ) = prm.daysallowedsendmessage "
             + "         and prm.whenToSend is null and prm.dateToCompare='" + dateToCompare + "' and prm.sendto = "
             + PatientReminder.SEND_TO_PATIENT;
     }
@@ -312,8 +312,8 @@
     private String sendToHealthWorkerSql( String dateToCompare )
     {
         return "SELECT pi.programinstanceid, uif.phonenumber, prm.templatemessage, p.name, org.name as orgunitName, "
-            + "   pg.name as programName, pi.dateofincident, pi.enrollmentdate,(now() - pi.enrollmentdate ) as days_since_erollment_date, "
-            + "       (now() - pi.dateofincident ) as days_since_incident_date "
+            + "   pg.name as programName, pi.dateofincident, pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, "
+            + "       (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date "
             + "    FROM patient p INNER JOIN programinstance pi "
             + "           ON p.patientid=pi.patientid INNER JOIN program pg "
             + "           ON pg.programid=pi.programid INNER JOIN organisationunit org "
@@ -323,16 +323,16 @@
             + "           ON us.userid=uif.userinfoid " + "    WHERE pi.status = " + ProgramInstance.STATUS_ACTIVE
             + "      and uif.phonenumber is not NULL and uif.phonenumber != '' "
             + "      and prm.templatemessage is not NULL and prm.templatemessage != '' "
-            + "      and pg.type=1 and prm.daysallowedsendmessage is not null " + "      and (now() - pi."
-            + dateToCompare + " ) = prm.daysallowedsendmessage " + " and prm.dateToCompare='" + dateToCompare
+            + "      and pg.type=1 and prm.daysallowedsendmessage is not null " + "      and ( DATE(now()) - DATE( pi."
+            + dateToCompare + " ) ) = prm.daysallowedsendmessage " + "      and prm.dateToCompare='" + dateToCompare
             + "'     and prm.whenToSend is null and prm.sendto =  " + PatientReminder.SEND_TO_HEALTH_WORKER;
     }
 
     private String sendMessageToOrgunitRegisteredSql( String dateToCompare )
     {
         return "SELECT pi.programinstanceid, org.phonenumber, prm.templatemessage, p.name, org.name as orgunitName, "
-            + "   pg.name as programName, pi.dateofincident, pi.enrollmentdate,(now() - pi.enrollmentdate ) as days_since_erollment_date, "
-            + "       (now() - pi.dateofincident ) as days_since_incident_date "
+            + "   pg.name as programName, pi.dateofincident, pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, "
+            + "       (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date "
             + "    FROM patient p INNER JOIN programinstance pi "
             + "           ON p.patientid=pi.patientid INNER JOIN program pg "
             + "           ON pg.programid=pi.programid INNER JOIN organisationunit org "
@@ -340,16 +340,16 @@
             + "           ON prm.programid = pi.programid " + "    WHERE pi.status = " + ProgramInstance.STATUS_ACTIVE
             + "      and org.phonenumber is not NULL and org.phonenumber != '' "
             + "      and prm.templatemessage is not NULL and prm.templatemessage != '' "
-            + "      and pg.type=1 and prm.daysallowedsendmessage is not null " + "      and ( now() -  pi."
-            + dateToCompare + "  ) = prm.daysallowedsendmessage " + " and prm.dateToCompare='" + dateToCompare
+            + "      and pg.type=1 and prm.daysallowedsendmessage is not null " + "      and ( DATE(now()) - DATE( pi."
+            + dateToCompare + " ) ) = prm.daysallowedsendmessage " + "      and prm.dateToCompare='" + dateToCompare
             + "'     and prm.whenToSend is null and prm.sendto =  " + PatientReminder.SEND_TO_ORGUGNIT_REGISTERED;
     }
 
     private String sendMessageToUsersSql( String dateToCompare )
     {
         return "SELECT pi.programinstanceid, uif.phonenumber, prm.templatemessage, p.name, org.name as orgunitName, pg.name as programName, pi.dateofincident ,"
-            + "pi.enrollmentdate,(now() - pi.enrollmentdate ) as days_since_erollment_date, "
-            + "(now() - pi.dateofincident ) as days_since_incident_date "
+            + "pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, "
+            + "(DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date "
             + "FROM patient p INNER JOIN programinstance pi "
             + "    ON p.patientid=pi.patientid INNER JOIN program pg "
             + "    ON pg.programid=pi.programid INNER JOIN organisationunit org "
@@ -362,19 +362,20 @@
             + "         and uif.phonenumber is not NULL and uif.phonenumber != '' "
             + "         and prm.templatemessage is not NULL and prm.templatemessage != '' "
             + "         and pg.type=1 and prm.daysallowedsendmessage is not null "
-            + "         and ( now() - pi."
+            + "         and ( DATE(now()) - DATE( "
             + dateToCompare
-            + " )  = prm.daysallowedsendmessage "
+            + " ) ) = prm.daysallowedsendmessage "
             + "         and prm.dateToCompare='"
             + dateToCompare
-            + "'        and prm.sendto = " + PatientReminder.SEND_TO_ALL_USERS_IN_ORGUGNIT_REGISTERED;
+            + "'        and prm.sendto = "
+            + PatientReminder.SEND_TO_ALL_USERS_IN_ORGUGNIT_REGISTERED;
     }
 
     private String sendMessageToUserGroupsSql( String dateToCompare )
     {
         return "select pi.programinstanceid, uif.phonenumber,prm.templatemessage, p.name, org.name as orgunitName ,"
-            + " pg.name as programName, pi.dateofincident, pi.enrollmentdate, (now() - pi.enrollmentdate ) as days_since_erollment_date, "
-            + "(now() - pi.dateofincident ) as days_since_incident_date "
+            + " pg.name as programName, pi.dateofincident, pi.enrollmentdate, (DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, "
+            + "(DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date "
             + "  from patient p INNER JOIN programinstance pi " + "       ON p.patientid=pi.patientid "
             + "   INNER JOIN program pg " + "       ON pg.programid=pi.programid "
             + "   INNER JOIN organisationunit org " + "       ON org.organisationunitid = p.organisationunitid "
@@ -383,9 +384,8 @@
             + "   INNER JOIN userinfo uif " + "       ON uif.userinfoid = ugm.userid " + "  WHERE pi.status= "
             + ProgramInstance.STATUS_ACTIVE + "       and uif.phonenumber is not NULL and uif.phonenumber != '' "
             + "       and prm.templatemessage is not NULL and prm.templatemessage != '' "
-            + "       and pg.type=1 and prm.daysallowedsendmessage is not null " + "       and (  now() - pi."
-            + dateToCompare + " ) = prm.daysallowedsendmessage " + "       and prm.whentosend is null "
+            + "       and pg.type=1 and prm.daysallowedsendmessage is not null " + "       and (  DATE(now()) - DATE("
+            + dateToCompare + ") ) = prm.daysallowedsendmessage " + "       and prm.whentosend is null "
             + "       and prm.sendto = " + PatientReminder.SEND_TO_USER_GROUP;
     }
-
 }

=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java	2013-11-25 03:07:49 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java	2013-12-02 03:34:33 +0000
@@ -574,7 +574,7 @@
     private String sendMessageToPatientSql()
     {
         return "select psi.programstageinstanceid, p.phonenumber, prm.templatemessage, p.name, org.name as orgunitName "
-            + ",pg.name as programName, ps.name as programStageName, psi.duedate,(now() - psi.duedate ) as days_since_due_date "
+            + ",pg.name as programName, ps.name as programStageName, psi.duedate,(DATE(now()) - DATE(psi.duedate) ) as days_since_due_date "
             + "from patient p INNER JOIN programinstance pi "
             + "     ON p.patientid=pi.patientid "
             + " INNER JOIN programstageinstance psi  "
@@ -587,13 +587,13 @@
             + "     ON org.organisationunitid = p.organisationunitid "
             + " INNER JOIN patientreminder prm  "
             + "     ON prm.programstageid = ps.programstageid "
-            + " WHERE pi.status="
+            + "WHERE pi.status="
             + ProgramInstance.STATUS_ACTIVE
             + "     and p.phonenumber is not NULL and p.phonenumber != '' "
             + "     and prm.templatemessage is not NULL and prm.templatemessage != '' "
             + "     and pg.type=1 and prm.daysallowedsendmessage is not null  "
             + "     and psi.executiondate is null "
-            + "     and ( now() - psi.duedate ) = prm.daysallowedsendmessage "
+            + "     and (  DATE(now()) - DATE(psi.duedate) ) = prm.daysallowedsendmessage "
             + "     and prm.whentosend is null and prm.sendto = " + PatientReminder.SEND_TO_PATIENT;
     }
 
@@ -601,7 +601,7 @@
     {
         return "SELECT psi.programstageinstanceid, uif.phonenumber, prm.templatemessage, p.name, org.name as orgunitName, "
             + "pg.name as programName, ps.name as programStageName, psi.duedate, "
-            + "         (now() - psi.duedate ) as days_since_due_date "
+            + "         (DATE(now()) - DATE(psi.duedate) ) as days_since_due_date "
             + " FROM patient p INNER JOIN programinstance pi "
             + "          ON p.patientid=pi.patientid "
             + "           INNER JOIN programstageinstance psi  "
@@ -624,7 +624,7 @@
             + "               and prm.templatemessage is not NULL and prm.templatemessage != '' "
             + "               and pg.type=1 and prm.daysallowedsendmessage is not null "
             + "               and psi.executiondate is null "
-            + "               and (  now() - psi.duedate ) = prm.daysallowedsendmessage "
+            + "               and (  DATE(now()) - DATE(psi.duedate) ) = prm.daysallowedsendmessage "
             + "               and prm.whentosend is null and prm.sendto = " + PatientReminder.SEND_TO_HEALTH_WORKER;
     }
 
@@ -632,7 +632,7 @@
     {
         return "select psi.programstageinstanceid, ou.phonenumber, prm.templatemessage, p.name, org.name as orgunitName, "
             + "pg.name as programName, ps.name as programStageName, psi.duedate,"
-            + "(now() - psi.duedate) as days_since_due_date "
+            + "(DATE(now()) - DATE(psi.duedate) ) as days_since_due_date "
             + "            from patient p INNER JOIN programinstance pi "
             + "               ON p.patientid=pi.patientid "
             + "           INNER JOIN programstageinstance psi "
@@ -653,7 +653,7 @@
             + "               and prm.templatemessage is not NULL and prm.templatemessage != '' "
             + "               and pg.type=1 and prm.daysallowedsendmessage is not null "
             + "               and psi.executiondate is null "
-            + "               and (now() - psi.duedate ) = prm.daysallowedsendmessage "
+            + "               and (  DATE(now()) - DATE(psi.duedate) ) = prm.daysallowedsendmessage "
             + "               and prm.whentosend is null and prm.sendto = "
             + +PatientReminder.SEND_TO_ORGUGNIT_REGISTERED;
     }
@@ -662,7 +662,7 @@
     {
         return "select psi.programstageinstanceid, uif.phonenumber,prm.templatemessage, p.name, org.name as orgunitName ,"
             + " pg.name as programName, ps.name as programStageName, psi.duedate, "
-            + "(now() - psi.duedate ) as days_since_due_date "
+            + "(DATE(now()) - DATE(psi.duedate) ) as days_since_due_date "
             + "  from patient p INNER JOIN programinstance pi "
             + "       ON p.patientid=pi.patientid "
             + "   INNER JOIN programstageinstance psi "
@@ -685,7 +685,7 @@
             + "       and prm.templatemessage is not NULL and prm.templatemessage != '' "
             + "       and pg.type=1 and prm.daysallowedsendmessage is not null "
             + "       and psi.executiondate is null "
-            + "       and ( now() - psi.duedate ) = prm.daysallowedsendmessage "
+            + "       and (  DATE(now()) - DATE(psi.duedate) ) = prm.daysallowedsendmessage "
             + "       and prm.whentosend is null and prm.sendto = "
             + PatientReminder.SEND_TO_ALL_USERS_IN_ORGUGNIT_REGISTERED;
     }
@@ -694,7 +694,7 @@
     {
         return "select psi.programstageinstanceid, uif.phonenumber,prm.templatemessage, p.name, org.name as orgunitName ,"
             + " pg.name as programName, ps.name as programStageName, psi.duedate, "
-            + "(now() - psi.duedate) as days_since_due_date "
+            + "(DATE(now()) - DATE(psi.duedate) ) as days_since_due_date "
             + "  from patient p INNER JOIN programinstance pi "
             + "       ON p.patientid=pi.patientid "
             + "   INNER JOIN programstageinstance psi "
@@ -717,7 +717,7 @@
             + "       and prm.templatemessage is not NULL and prm.templatemessage != '' "
             + "       and pg.type=1 and prm.daysallowedsendmessage is not null "
             + "       and psi.executiondate is not null "
-            + "       and (  now() - psi.duedate ) = prm.daysallowedsendmessage "
+            + "       and (  DATE(now()) - DATE(psi.duedate) ) = prm.daysallowedsendmessage "
             + "       and prm.whentosend is null " + "       and prm.sendto = " + PatientReminder.SEND_TO_USER_GROUP;
     }