Calculate number of days between two dates

PHP
1

I’m trying to calculate the number of days between two dates that I’m pulling from my SQL server. I’m not having any success accessing the $row values within my if statement. I can var_dump within the if statement, however, I’m unable to echo out the values associated with any of the column names, much less perform any calculations with them. Any help would be much appreciated, or if you have any suggested reading I’d be much obliged.

if (isset($_POST['submit'])) {
    $selectDates = "SELECT DisclosureDate,interviewDate,FindingsDate,SubmittedDate,FiledDate,DispositionDate FROM ActiveCases";
    $submitToDB = sqlsrv_query($db, $selectDates);
    while ($row = sqlsrv_fetch_array($submitToDB, SQLSRV_FETCH_ASSOC)) {

        // As we're going through each row the time between firstDateField selected (e.g. Disclosure Date)
// and secondDateField (e.g Disposition date) must be calculated and the duration printed along with the a row number?
        if ($_POST['firstDateField'] = "DisclosureDate1" && $_POST['secondDateField'] = 'DispositionDate2"') {
            echo "hello ";
//        Calculate number of days between the 2 dates
            var_dump($row);

            echo "<pre>";
//        echo $row["DisclosureDate"];
            echo "<pre>";

            die(1);
//        echo $firstDate;
//        $secondDate = $row['DispositionDate'];
//        $numberOfDays = $firstDate - $secondDate;
//        echo $firstDate . "<br />";

        }
//    echo "hello";
//    die(1);
    }


}
?>

    <div>
        <!--Need to calculate number of days between Disclosure date and Interview Date, etc.-->
        <form action="AltReport.php" method="POST">
            <h2>Select date fields:</h2>
            <table>
                <tr>
                    <td>
                        <table width='100%' id='input'>
                            <tr>
                                <td>
                                    <select id="show" name='firstDateField'>
                                        <option value="DisclosureDate1">Disclosure Date</option>
                                        <!--                                        <option value="CACReferralDate">CAC Referral Date</option>-->
                                        <!--                                        <option value="interviewDate">Interview Date</option>-->
                                        <!--                                        <option value="VictimName">Victim Name</option>-->
                                        <!--                                        <option value="VictimDOB">Victim DOB</option>-->
                                        <!--                                        <option value="DefendentName">Defendant Name</option>-->
                                        <!--                                        <option value="DefendentDOB">Defendant DOB</option>-->
                                        <!--                                        <option value="Abuse">Type of abuse</option>-->
                                        <!--                                        <option value="ReferralDate">Referral Date to CD</option>-->
                                        <!--                                        <option value="CDCaseNum">CD Case Number</option>-->
                                        <!--                                        <option value="Findings">CD Findings</option>-->
                                        <!--                                        <option value="FindingsDate">CD Findings Date</option>-->
                                        <!--                                        <option value="Law">Law Enforcement</option>-->
<!--                                        <option value="SubmittedDate1">Submitted/ Reviewed Date</option>-->
                                        <!--                                        <option value="FiledDate">Filed/ Not filed Date</option>-->
                                        <!--                                        <option value="Disp">Disposition</option>-->
<!--                                        <option value="DispositionDate1">Disposition Date</option>-->
                                        <!--                                        <option value="Determination">Determination</option>-->
                                        <!--                                        <option value="Archive">Archived</option>-->
                                        <!--                                        <option value="Closed">Closed</option>-->
                                    </select>
                                    <!--                    <input class="datepicker2">-->
                                    <select id="show" name='secondDateField'>
                                        <!--                                        <option value="DisclosureDate2">Disclosure Date</option>-->
                                        <!--                                        <option value="CACReferralDate">CAC Referral Date</option>-->
                                        <!--                                        <option value="interviewDate">Interview Date</option>-->
                                        <!--                                        <option value="VictimName">Victim Name</option>-->
                                        <!--                                        <option value="VictimDOB">Victim DOB</option>-->
                                        <!--                                        <option value="DefendentName">Defendant Name</option>-->
                                        <!--                                        <option value="DefendentDOB">Defendant DOB</option>-->
                                        <!--                                        <option value="Abuse">Type of abuse</option>-->
                                        <!--                                        <option value="ReferralDate">Referral Date to CD</option>-->
                                        <!--                                        <option value="CDCaseNum">CD Case Number</option>-->
                                        <!--                                        <option value="Findings">CD Findings</option>-->
                                        <!--                                        <option value="FindingsDate">CD Findings Date</option>-->
                                        <!--                                        <option value="Law">Law Enforcement</option>-->
                                        <!--                                                                                <option value="SubmittedDate2">Submitted/ Reviewed Date</option>-->
                                        <!--                                        <option value="FiledDate">Filed/ Not filed Date</option>-->
                                        <!--                                        <option value="Disp">Disposition</option>-->
                                        <option value="DispositionDate2">Disposition Date</option>
                                        <!--                                        <option value="Determination">Determination</option>-->
                                        <!--                                        <option value="Archive">Archived</option>-->
                                        <!--                                        <option value="Closed">Closed</option>-->
                                    </select>
                                    <!--                                    <input class="datepicker2">-->
                                </td>
                            </tr>
                        </table>
                    </td>
                    <td valign='top'><input type='submit' name="submit" onClick="addNew();" value='Generate report'/>
                    </td>
                </tr>
            </table>

        </form>

    </div>
1 Like
2

IF statements require OPERANDS ( ==, != , === , !==, etc.)

        if ($_POST['firstDateField'] = "DisclosureDate1" && $_POST['secondDateField'] = 'DispositionDate2"') {...}

should be:

    if ($_POST['firstDateField'] ===  "DisclosureDate1" && $_POST['secondDateField'] === 'DispositionDate2"') {...}

Hope that helps.

PS.

Some logic for getting the number of days:
Assuming your DB is storing dates as a TIME STAMP, you could take the ABSOLUTE VALUE of the DIFFERENCE between the two dates and then divided by 86400 then floor() the result.

function deltaDays(time1, $time2){ return floor(abs(($time1-$time2)/86400));}

Hope that helps

1 Like
3

@dresden_phoenix Thanks so much for your prompt reply. I had tried using the comparison operators as you described (===) as well as (==) and it made no difference. For some reason I’m not able to access the $row[“DisclosureDate”] inside the if statement OR immediately following the opening of the while block. In fact, the script breaks if I move "echo $row[“DisclosureDate”] up immediately above the if statement. :neutral_face:

4

you have to break this more down: what are the results of var_dump($_POST) and var_dump($row)? there may also be a typo in your second condition at the last character.

5

Can’t you make SQL Server calculate the difference? There sure are functions for date calculations.

1 Like
6

Doesn’t this line

 if ($_POST['firstDateField'] === "DisclosureDate1" && $_POST['secondDateField'] === 'DispositionDate2"') {
                                    scroll that way  -->                                         here ^

fail because you’re comparing the second field to a quoted string that contains a double-quote as the last character, and that isn’t what your HTML defines the value as? (ETA - as @chorn mentioned above)

Also both your select tags have an id of “show”, is that allowed in HTML now? I’m a bit rusty on it, but last time I wasn’t, ids had to be unique.

Can you expand on “breaks”? What happens? Do you get error messages?

7

Still not allowed.

8

@Dormilich Yessir. I may look into using SQL Server to do the calculations if I’m unable to do so in php. Also, is there a way to edit my original post to show syntax changes, etc.?

9

@droopsnoot I have changed the id tags to “show1” and “show2” in my source (how do I edit my original post to show the changes?).

By “breaks” I mean that the "include_once(“includes/header.html”); executes fine, but nothing else is loaded (the body is empty). No error messages.

10

@chorn Thank you for getting back with me and sorry for the delay in responding. As a full-time student, I’m only able to work on this project as studies allow. Also, how do I edit my original post to reflect current code I’ve got?

Contents of var_dump($_POST):
array(3) {
[“firstDateField”]=>
string(15) “DisclosureDate1”
[“secondDateField”]=>
string(16) “DispositionDate2”
[“submit”]=>
string(15) “Generate report”
}

var_dump($row):
array(6) {
[“DisclosureDate”]=>
object(DateTime)#1 (3) {
[“date”]=>
string(19) “2017-01-04 00:00:00”
[“timezone_type”]=>
int(3)
[“timezone”]=>
string(19) “America/Tegucigalpa”
}
[“interviewDate”]=>
object(DateTime)#2 (3) {
[“date”]=>
string(19) “2017-01-05 00:00:00”
[“timezone_type”]=>
int(3)
[“timezone”]=>
string(19) “America/Tegucigalpa”
}
[“FindingsDate”]=>
NULL
[“SubmittedDate”]=>
NULL
[“FiledDate”]=>
NULL
[“DispositionDate”]=>
NULL
}

1 Like
11

You can’t, once it gets past a certain age. I’m not sure how long.

It looks like the reason you can’t echo $row['DisclosureDate'] is that it’s an object, not a string. Can you echo $row['DisclosureDate']['date']?

12

Okay, so maybe we’re getting somewhere now. (I brought in ini_set(‘display_errors’, 1)

When I try to echo $row[‘DisclosureDate’] I get:
“Catchable fatal error: Object of class DateTime could not be converted to string in C:\AltReport.php on line 40”

when I echo $row[“DisclosureDate”][“Date”]; I get:
fatal error: Cannot use object of type DateTime as array in C:\AltReport.php on line 40

Okay, so I’ve been doing some searching and came across this article: https://stackoverflow.com/questions/7477943/how-to-retrieve-a-sql-datetime-object-with-a-php-row

After adding in:
echo date(“m/d/Y”, strtotime($row[“DisclosureDate”]));

I’m getting:
12/31/1969 for every entry (same as the original poster on the stackoverflow thread).

13

OK, how about

echo $row['DisclosureDate']->format('m-d-Y');

or: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ff07dab3-16e2-4d88-8029-ffbf0dfc93e5/converting-dates-for-use-by-php?forum=sqldriverforphp

1 Like
14

@droopsnoot That was it (echo $row[‘DisclosureDate’]->format(‘m-d-Y’); !!

BTW, sorry for the multiple replies. Sitepoint just notified me that it’s best to simply edit a reply instead of creating new ones.

I’ve backed up a couple steps and am now wondering if I can create a function that will handle the various dates that I’d like to pass to it. Although I’m successfully using the DATEDIFF sql function (see commented out code which includes the calculated value from SQL ‘DiscToCACReferralDate’), it seems like I should be able to simplify things with a function, however, when I try to call the function (calculateDifferenceBetweenDates - immediately after the if($_Post[‘firstDateField’] === “DisclosureDate”…) I get a “Fatal error: Call to undefined function calculateDifferenceBetweenDates()” Any idea what I’m doing wrong here. I’ve been researching several articles over the past couple hours to no avail. :-/

@droopsnoot Edit: if I move the function call down below the first if statement and move the function definition up in the if block, I don’t get the Fatal error, but it defeats the purpose of the function to handle various arguments.

 while ($row = sqlsrv_fetch_array($submitToDB, SQLSRV_FETCH_ASSOC)) {

//        echo $row["DisclosureDate"]["Date"];
        // As we're going through each row the time between firstDateField selected (e.g. Disclosure Date)
// and secondDateField (e.g Disposition date) must be calculated and the duration printed along with the a row number?

//        function to handle 2 date fields
        if ($_POST['firstDateField'] === "DisclosureDate1" && $_POST['secondDateField'] === "CACReferralDate2") {


            calculateDifferenceBetweenDates($row['DisclosureDate'], $row['CACReferralDate']);//    echo "<pre>";
        }
        function calculateDifferenceBetweenDates($date1, $date2)
        {
            echo "<br />";
//            $iterations += 1;

//            echo $iterations . " ";
            echo(date_diff($date1, $date2)->format('m-d-Y'));

            if ($date1 == null) {
                echo "No Date ";
            } else {
                echo $date1->format('m-d-Y');
            }
//
            echo " ";
            if ($date2 == null) {
                echo "No Date ";
                echo "<br />";
            } else {

                echo $date2->format('m-d-Y');
//
                echo " ";
//
                echo "<br />";
            }
        }

//        if ($_POST['firstDateField'] === "DisclosureDate1" && $_POST['secondDateField'] === "CACReferralDate2") {
//            echo "<br />";
//            $iterations += 1;
//
//            echo $iterations . " ";
//
//            if ($row['DisclosureDate'] == null){
//                echo "No Date ";
//            } else {
//                echo $row['DisclosureDate']->format('m-d-Y');
//            }
////
//            echo " ";
//            if ($row['CACReferralDate'] == null) {
//                echo "No Date ";
//                echo "<br />";
//            } else {
//
//                echo $row['CACReferralDate']->format('m-d-Y');
////
//                echo " ";
//                echo $row['DiscToCACReferralDate'];
////
//                echo "<br />";
//            }
//
//        }

    }
15

@droopsnoot BTW, thanks a heap!! Who knows how long I would have been searching before arriving at the clue you gave me. :slight_smile:

16

@Dormilich I really appreciate your suggestion. I needed to echo out the dates, so thankfully, droopsnoot and others have helped to that end. Now, per your suggestion, I’ve been able to use the DATEDIFF function to get SQL to calculate the difference. Many thanks to all who helped me with this!!! :smiley:

17

To me, the function definition should be completely outside of your program logic, it certainly shouldn’t be inside a while() loop. The physical position doesn’t really matter, except that I’m always concerned that if you put it inside something like an if() conditional and try to call it from outside that structure, it might throw an undefined error. In any case, it makes for confusion because positioning it inside program logic suggests that it’s somehow linked to that logic when, as you said, the whole point is to call it from anywhere.

So, move the function definition to the start of your PHP code, just after the session_start() if you have one, and just call it from wherever you want to call it. Once you’ve got a few of these functions, and more than one PHP script to call them from, you can move just the functions out to a separate physical text file and use include or require to incorporate them into each script.

While you’re playing with that function, I personally wouldn’t choose to have it output stuff. Right now, you’re only using the function to calculate a difference and then display it, but what about later on when you have other uses, still want to calculate that difference, but don’t want it displayed in the same way? Far better IMO to have the function return the difference or an appropriate error code, and have the calling code decide how to present the results.

ETA - on that, if you were thinking that the function definition needs to be where it is so that the output appears in the correct order, that’s not the case - if you move the function to the start of the file that contains your PHP, it will still give the output in the correct place because it’s the program flow that dictates the order.

Thanks, though I have to admit that the only thing I was more successful at there was putting the question into Google. I’ve never connected to SQL server from PHP, so it’s not as if I actually know what I’m talking about. :slight_smile:

1 Like
18

@droopsnoot Man you have no idea how much help your last post was. Moving my function outside of the program logic was HUGE and I’ve finally got it working seamlessly. Many thanks!!! :grinning::+1:

Side note: I have yet to restructure my code per your suggestion as this is a fairly small project that I don’t foresee making any significant changes to in the future, but I’ve included your recent post in my code (as a comment) for reference in the event that the project grows. Thanks again!!

1 Like
19

My preference is always to allow a framework or library to do all the heavy lifting for me. In that regards an alternative approach would be using Carbon which has methods to do exactly what you need plus much more.

21

@ZooKeeper Thank you for your suggestion! I’ve checked out Carbon and it looks like a great resource. I’ll have to do some further research to see if I can incorporate it into my system.