Alright. I am stumped, and would like some fresh air to this problem.

This is a personal app I am making for our company which will allow me to select all data between the 2 dates chosen.

like 01/12/2011 to 31/12/2011.

this is the script I am currently working on.


//01/12/2012 Date/Month/Year

$st_date  = date("d/m/Y", strtotime($_POST['date-from']));
$en_date  = date("d/m/Y", strtotime($_POST['date-to']));

$db->query("SELECT * FROM site_data WHERE party='$prty' AND date BETWEEN '$st_date' AND '$en_date'");

Basically, the date in MYSQL is set up as 20/12/2011 in String Format. When I enter that into the search, it does not retrieve any data back.

in all means terms I want to select all data from 01/12/2011 thru 31/12/2011

When I enter in the date into the form it shows the data with proper date but mixed with 2012 Year , i have tried so many ways but I have no clue how to implement this.

Please help!!!
Thanks in advance!

    macgadger;10994356 wrote:

    Basically, the date in MYSQL is set up as 20/12/2011 in String Format.

    And therein lies your problem; why aren't you using a DATE column?

    Doing so would allow you to take advantage of MySQL's many date-related features... such how it handles dates in the BETWEEN syntax.

      3 months later

      hi,

      date range also is my problem. i tried script that you given unfortunately i had an error

      Fatal error: Call to undefined function startotime() in C:\xampp\htdocs\intranet\search\search_sickleave.php on line 47

      here is my code .

      <?php
$start = date("Y-m-d", startotime($_POST["datefrom"]));
$end  = date("Y-m-d", startotime($_POST["dateto"]));

	// Search By daterange  

$strSQL = "SELECT * FROM leave WHERE datefiled between '".$start."' and '".$end."'"; 
$objQuery = mssql_query($strSQL) or die ("Error Query [".$strSQL."]");  
?>

		<br />		<br />
		<TABLE width="100%" align="left" class="gridtable">

		<tr bgcolor="#0066CC" height="25">

		<TH width="8px">Employee Name</TH>
		<TH width ="25px">Reqno</TH>
		<TH width = "15px">Date Filed</TH>
		<TH width = "15px">Start Date</TH>
		<TH width = "15px">End Date</TH>
		<TH width = "15px">Days</TH>
		<TH width = "15px">Reason</TH>
		<?php
		while ($objectresult = mssql_fetch_array($objQuery))
		{
		?>
				<tr>
				<td width="100"><?= ($objectresult["empname"]); ?></td>
				<td width="100"><?= ($objectresult["reqno"]); ?></td>
				<td width="100"><?= ($objectresult["datefiled"]); ?></td>
				<td width="100"><?= ($objectresult["startdate"]); ?></td>
				<td width="100"><?= ($objectresult["enddate"]); ?></td>
				<td width="100"><?= ($objectresult["sldays"]); ?></td>
				<td width="100"><?= ($objectresult["reason"]); ?></td>
				</tr>
		<?php
		}
		?>

      hope you can help also with my problem..thank you

        I think you meant to use strtotime not startotime()

          yeah...sorry sir...already fixed the problem....so many thanks to you!!!! 😃

            bradgrafelman;10994358 wrote:

            And therein lies your problem; why aren't you using a DATE column?

            Doing so would allow you to take advantage of MySQL's many date-related features... such how it handles dates in the BETWEEN syntax.

            hi sir, i would like to ask about the date range.. when im trying to search
            2012-04-03 to 2012-04-10 the data will comes out but when i try to search
            2012-04-03 to 2012-04-03 only nothing comes out to my table.

            thanks

              kickapoo;11001483 wrote:

              2012-04-03 to 2012-04-03 only nothing comes out to my table.

              Are you using DATE or DATETIME?

                Your query should work with a DATE column but it wouldn't work with a DATETIME column.

                I think you have another problem.

                  here is my code
                  <?php
                  $start = date("d-m-Y", strtotime($GET["datefrom"]));
                  $end = date("d-m-Y", strtotime($                  GET["dateto"]));

                  // Search By daterange  

$strSQL = "SELECT * FROM leave WHERE datefiled between '".$start."' and '".$end."'"; 
$objQuery = mssql_query($strSQL) or die ("Error Query [".$strSQL."]");  
?>

                    MySQL is expecting the DATE column to be YYYY-MM-DD so your date() function should be using 'Y-m-d'

                      m@tt;11001497 wrote:

                      MySQL is expecting the DATE column to be YYYY-MM-DD so your date() function should be using 'Y-m-d'

                      hi m@tt it works but Y-m-d is for mysql but im using mssql...Y-m-d is date rule for php?

                        Just look at the values in your database. What is the format?

                        I am not familiar with MSSQL but I believe it also expects either YYYY-MM-DD or YYYYMMDD

                          kickapoo;11001498 wrote:

                          hi m@tt it works but Y-m-d is for mysql but im using mssql

                          According to the documentation I found, they both use the same format - ISO 8601.

                            Note also that you asked this question in a thread that explicitly said it related to MySQL...

                              Write a Reply...