none
[SOLVED] How to retrieve records between a date range... plus retrieving data in a specific date format

    Question

  •  

    Hi,

    First question... in SQL Server 2008, I have a 'start_date' field that is of datatype 'date'. Although I write to the db a string in the format mm-dd-yyyy, it stores it as yyyy-mm-dd in the database. If it won't store it any other way, how can I at least retrieve it in a sql statement in the format dd-mm-yyyy ? I see the SELECT convert(varchar, getdate()..) solution, but I must not know how to use it correctly (maybe it only works on varchars, and my field is a 'date' type... I'm not sure), because it's not working for me.

     

     

    $query = "SELECT id, start_date FROM mytable"
    

     

    I want that to return something like id, dd-mm-yyyy

    So I would use something like:

     

    convert(varchar, getdate(), 103)

     

    But I don't know where to put that so that my start_date variable is converted to the correct format. Any ideas?


    Secondly, I am using a PHP script that declares a start and end date as two separate string variables. Is there any way to pull only records that are within that range using SQL?




    Kevin



    • Edited by kevin-berry Wednesday, June 08, 2011 3:29 AM
    Tuesday, June 07, 2011 8:22 PM

Answers

  • SELECT id, convert(varchar, start_date , 103) FROM mytable
    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    • Marked as answer by kevin-berry Wednesday, June 08, 2011 3:29 AM
    Tuesday, June 07, 2011 8:26 PM
  • Hi,

    So, here's my code after all's said and done. A BIG THANK YOU TO THOSE WHO HELPED ME. Much appreciated. I've been struggling with this for a few days now, so it's good to finally get it solved. For those in the future who find themselves in my position, I hope they find this post useful and informative.

     

    In my SQL Server 2008 database, I have a 'start_date' column that is of datatype 'date'. The format the 'date' datatype seems to store things in is yyyy-mm-dd, despite my inserting into the database (via a PHP script) the string as dd-mm-yyyy.

    Anyhow, so we have that date. I have a PHP script that has two dates in dd-mm-yyyy format, a start date and and end date. I want to check my database to see if the date stored falls between those two days. So I used a query like this, and it works fine.

     

    SELECT id, convert(varchar, start_date, 103) AS 'start_date', 
    FROM Table1
    WHERE start_date BETWEEN convert(date, '16/03/2008', 103) AND convert(date, '07/06/2011', 103)

     

     

    Hope that helps! If anyone happens to stumble upon a similar "I am so confused" moment like myself, here's my complete implementation using two jquery calendars to select the start and end dates, pass it to a php script, and get the dates falling within that range from my db and displaying it on the webpage. I make no claim that my code is efficient or well written - to a seasoned PHP/Javascript coder, this probably looks like inefficient piecemeal - I'm happy to learn though, if someone wants to point out my fallacies.

    HTML FILE... Creates a page that has a date range picker much like this (http://jqueryui.com/demos/datepicker/#date-range), and displays the output in the "results" div below. Default dates are set, the database is queried within that date range for entries in the db that fall within the range. The user changes a date in either box, and the results update accordingly with no pressing of a button.

     

     

    <html>
    	<head>
    		<link type="text/css" href="jquery-ui-1.8.13.custom/css/custom-theme/jquery-ui-1.8.13.custom.css" rel="stylesheet" />
    		<script type="text/javascript" src="jquery-ui-1.8.13.custom/js/jquery-1.5.1.min.js"></script>
    		<script type="text/javascript" src="jquery-ui-1.8.13.custom/js/jquery-ui-1.8.13.custom.min.js"></script>
    		<link rel="stylesheet" type="text/css" href="css/stylesheet.css" />
    		<script>
    			$(function() {
    			
    		function updateResults() 
    			{
      			$('#results').load('test-datepickerresults.php', // Load response from results.php into #results element
       	// With these parameters
       	{from_date: fromDate.datepicker('getDate').toString(), to_date: toDate.datepicker('getDate').toString()});
    			}
    			
    				//create a couple of date textboxes
      			$.datepicker.setDefaults
    				({
    					dateFormat: 'dd/mm/yy', defaultDate: '-1w', changeMonth: true, changeYear: true, 
    					maxDate: 0, showButtonPanel: true, showWeek: true
    				});
    				
    				var fromDate = $('#from').datepicker
    				({
    					onSelect: function() 
    					{
    						var option = this.id == 'from' ? 'minDate': 'maxDate';
    						toDate.not(this).datepicker('option', option, $(this).datepicker('getDate'));
         		updateResults(); // When a new date is selected update the results
       		}
    				});
    				
    				var toDate = $('#to').datepicker
    				({
    					onSelect: function() 
    					{
         		updateResults(); // When a new date is selected update the results
       		}
    				});
    			
    				//Set the default from and to dates.
      			fromDate.datepicker('setDate', '-1w');
      			toDate.datepicker('setDate', '+0');
      			updateResults(); // Initial call on load
    			});
    		</script>
    	</head>
    <body>
    	<div id="Dates">
    		<label for="from"><b>Start Date:</b></label>
    		<input type="text" id="from" name="from"/>
    		<label for="to"><b>End Date:</b></label>
    		<input type="text" id="to" name="to"/>
    		<br><br><br><br>
    		<div id="results"></div>
    	</div>
    </body>
    </html>
    

     

     

    PHP FILE THAT'S CALLED WHENEVER A NEW DATE IS PICKED, THUS UPDATING THE RESULTS IN REALTIME:

     

    <?php
    $from = $_POST['from_date'];
    $to = $_POST['to_date'];
    
    //----------------------------------------------//
    //		Explode and format the Date	//
    //----------------------------------------------//
    
    	function leading_zeros($value, $places)
    	{
    		// Function written by Marcus L. Griswold (vujsa)
    		// Can be found at http://www.handyphp.com
    		$leading = NULL;
    		if(is_numeric($value)){
    			for($x = 1; $x <= $places; $x++){
    				$ceiling = pow(10, $x);
    				if($value < $ceiling){
    					$zeros = $places - $x;
    					for($y = 1; $y <= $zeros; $y++) { $leading .= "0"; }
    					$x = $places + 1;
    				}
    			}
    			$output = $leading . $value;
    		}
    		else
    			$output = $value;
    		return $output;
    	}
    
    $month = array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
    
    	//Explode $from with a space ' ' as the delimiter
    	$from = explode(" ", $from);
    	$to = explode(" ", $to);
    	$from_month = NULL;
    	$to_month = NULL;
    	
    $counter=0;
    while($counter < sizeof($month))
    {
    	$similarity = levenshtein($month[$counter], $from[1]);
    	if($similarity == 0)
    		$from_month = leading_zeros(($counter+1),2);
    		
    	$similarity = levenshtein($month[$counter], $to[1]);
    	if($similarity == 0)
    		$to_month = leading_zeros(($counter+1),2);
    	
    	if($from_month != "" && $to_month !="")
    		break;
    	else
    		$counter++;
    }
    
    $from_date = "$from[2]/$from_month/$from[3]";
    $to_date = "$to[2]/$to_month/$to[3]";
    echo "Start Date: $from_date<br>End Date: $to_date<br>";
    echo "<br>Records that fall within that date range:";
    
    //----------------------------------------------------------------------------------------------------//
    //				Establish Connection to Server & Database 		  	  //
    //----------------------------------------------------------------------------------------------------//
    
    	//speficy server name and database
    	$serverName = "(local)";
    	$connectionInfo = array( 'Database'=>'MyDB');
    
    	//Connect using Windows Authentication.
    	$conn = sqlsrv_connect( $serverName, $connectionInfo);
    
    	if( $conn === false )
    	{
    	  echo "Unable to connect.</br>";
    	  die( print_r( sqlsrv_errors(), true));
    	}
    
    //----------------------------------------------------------------------------------------------------//
    //			SQL Query to Retrieve the Attributes of Relevant CrimeNode Entries	  	  //
    //----------------------------------------------------------------------------------------------------//
    
    date_default_timezone_set('yourtimezonecodehere');
    
    	//declare the SQL statement that will query the database
    	//convert(varchar, getdate(), 103) puts the default format, yyyy-mm-dd into dd/mm/yyyy
    	$query = "SELECT 	id, 
    				convert(varchar, incident_start_date, 103) AS 'start_date'";
    	$query .= "FROM 	table1 ";
    	$query .= "WHERE	table1.start_date BETWEEN convert(date, '".$from_date."', 103) AND convert(date, '".$to_date."', 103)";
    
    echo "<br><br>" . $query . "<br><br>";
    
    	//execute the SQL query and return records
    	//SQLSRV driver requires array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ) 
    	//parameters in order to store number of rows... not sure why.
    	$result = sqlsrv_query($conn, $query, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
    
    
    //----------------------------------------------------------------------------------------------------//
    //				Store SQL Query Table Attribute Names 			  	  //
    //----------------------------------------------------------------------------------------------------//
    
    $field_name = array();
    foreach(sqlsrv_field_metadata($result) as $fieldMetadata)
    {
    	$counter=4;
    	foreach($fieldMetadata as $name => $value)
    	{
    		if($counter % 4 == 0)
    		{
      		array_push($field_name, $value);
    			$counter++;
    		}
    	}
    }
    
    echo "<br><br>";
    
    //----------------------------------------------------------------------------------------------------//
    //				Print Results in a Table				  	  //
    //----------------------------------------------------------------------------------------------------//
    
    echo "<table border='1'><tr>";
    $counter=0;
    while($counter < sizeof($field_name))
    {
    	echo"<th><font size=\"1\">$field_name[$counter]</th>";
    	$counter++;
    }
    echo "</tr>";
    while($row = sqlsrv_fetch_array($result))
    {
    	echo "<tr>";
    	$field_name_counter=0;
    	while($field_name_counter<sizeof($field_name))
    	{
    		echo "<td>$row[$field_name_counter]</td>";
    		$field_name_counter++;
    	}
    	echo "</tr>";
    }
    echo "</table>";
    
    
    
    ?>
    

     

     


    • Marked as answer by kevin-berry Wednesday, June 08, 2011 3:29 AM
    Wednesday, June 08, 2011 2:25 AM

All replies

  • SELECT id, convert(varchar, start_date , 103) FROM mytable
    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    • Marked as answer by kevin-berry Wednesday, June 08, 2011 3:29 AM
    Tuesday, June 07, 2011 8:26 PM
  • EDIT: Thanks KZ, you solved part of it.

     

    The other part I was wondering about is:

     

    I have two PHP string variables, from_date, to_date in the format dd-mm-yyyy. Is it possible to query SQL to determine if the date in the db falls within that range?

    Tuesday, June 07, 2011 8:39 PM
  • declare

    @query varchar(50

    )

    set

    @query = convert(varchar, getdate(), 103

    )

    select

    @query

    Tuesday, June 07, 2011 9:03 PM
  • Hi,

    So, here's my code after all's said and done. A BIG THANK YOU TO THOSE WHO HELPED ME. Much appreciated. I've been struggling with this for a few days now, so it's good to finally get it solved. For those in the future who find themselves in my position, I hope they find this post useful and informative.

     

    In my SQL Server 2008 database, I have a 'start_date' column that is of datatype 'date'. The format the 'date' datatype seems to store things in is yyyy-mm-dd, despite my inserting into the database (via a PHP script) the string as dd-mm-yyyy.

    Anyhow, so we have that date. I have a PHP script that has two dates in dd-mm-yyyy format, a start date and and end date. I want to check my database to see if the date stored falls between those two days. So I used a query like this, and it works fine.

     

    SELECT id, convert(varchar, start_date, 103) AS 'start_date', 
    FROM Table1
    WHERE start_date BETWEEN convert(date, '16/03/2008', 103) AND convert(date, '07/06/2011', 103)

     

     

    Hope that helps! If anyone happens to stumble upon a similar "I am so confused" moment like myself, here's my complete implementation using two jquery calendars to select the start and end dates, pass it to a php script, and get the dates falling within that range from my db and displaying it on the webpage. I make no claim that my code is efficient or well written - to a seasoned PHP/Javascript coder, this probably looks like inefficient piecemeal - I'm happy to learn though, if someone wants to point out my fallacies.

    HTML FILE... Creates a page that has a date range picker much like this (http://jqueryui.com/demos/datepicker/#date-range), and displays the output in the "results" div below. Default dates are set, the database is queried within that date range for entries in the db that fall within the range. The user changes a date in either box, and the results update accordingly with no pressing of a button.

     

     

    <html>
    	<head>
    		<link type="text/css" href="jquery-ui-1.8.13.custom/css/custom-theme/jquery-ui-1.8.13.custom.css" rel="stylesheet" />
    		<script type="text/javascript" src="jquery-ui-1.8.13.custom/js/jquery-1.5.1.min.js"></script>
    		<script type="text/javascript" src="jquery-ui-1.8.13.custom/js/jquery-ui-1.8.13.custom.min.js"></script>
    		<link rel="stylesheet" type="text/css" href="css/stylesheet.css" />
    		<script>
    			$(function() {
    			
    		function updateResults() 
    			{
      			$('#results').load('test-datepickerresults.php', // Load response from results.php into #results element
       	// With these parameters
       	{from_date: fromDate.datepicker('getDate').toString(), to_date: toDate.datepicker('getDate').toString()});
    			}
    			
    				//create a couple of date textboxes
      			$.datepicker.setDefaults
    				({
    					dateFormat: 'dd/mm/yy', defaultDate: '-1w', changeMonth: true, changeYear: true, 
    					maxDate: 0, showButtonPanel: true, showWeek: true
    				});
    				
    				var fromDate = $('#from').datepicker
    				({
    					onSelect: function() 
    					{
    						var option = this.id == 'from' ? 'minDate': 'maxDate';
    						toDate.not(this).datepicker('option', option, $(this).datepicker('getDate'));
         		updateResults(); // When a new date is selected update the results
       		}
    				});
    				
    				var toDate = $('#to').datepicker
    				({
    					onSelect: function() 
    					{
         		updateResults(); // When a new date is selected update the results
       		}
    				});
    			
    				//Set the default from and to dates.
      			fromDate.datepicker('setDate', '-1w');
      			toDate.datepicker('setDate', '+0');
      			updateResults(); // Initial call on load
    			});
    		</script>
    	</head>
    <body>
    	<div id="Dates">
    		<label for="from"><b>Start Date:</b></label>
    		<input type="text" id="from" name="from"/>
    		<label for="to"><b>End Date:</b></label>
    		<input type="text" id="to" name="to"/>
    		<br><br><br><br>
    		<div id="results"></div>
    	</div>
    </body>
    </html>
    

     

     

    PHP FILE THAT'S CALLED WHENEVER A NEW DATE IS PICKED, THUS UPDATING THE RESULTS IN REALTIME:

     

    <?php
    $from = $_POST['from_date'];
    $to = $_POST['to_date'];
    
    //----------------------------------------------//
    //		Explode and format the Date	//
    //----------------------------------------------//
    
    	function leading_zeros($value, $places)
    	{
    		// Function written by Marcus L. Griswold (vujsa)
    		// Can be found at http://www.handyphp.com
    		$leading = NULL;
    		if(is_numeric($value)){
    			for($x = 1; $x <= $places; $x++){
    				$ceiling = pow(10, $x);
    				if($value < $ceiling){
    					$zeros = $places - $x;
    					for($y = 1; $y <= $zeros; $y++) { $leading .= "0"; }
    					$x = $places + 1;
    				}
    			}
    			$output = $leading . $value;
    		}
    		else
    			$output = $value;
    		return $output;
    	}
    
    $month = array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
    
    	//Explode $from with a space ' ' as the delimiter
    	$from = explode(" ", $from);
    	$to = explode(" ", $to);
    	$from_month = NULL;
    	$to_month = NULL;
    	
    $counter=0;
    while($counter < sizeof($month))
    {
    	$similarity = levenshtein($month[$counter], $from[1]);
    	if($similarity == 0)
    		$from_month = leading_zeros(($counter+1),2);
    		
    	$similarity = levenshtein($month[$counter], $to[1]);
    	if($similarity == 0)
    		$to_month = leading_zeros(($counter+1),2);
    	
    	if($from_month != "" && $to_month !="")
    		break;
    	else
    		$counter++;
    }
    
    $from_date = "$from[2]/$from_month/$from[3]";
    $to_date = "$to[2]/$to_month/$to[3]";
    echo "Start Date: $from_date<br>End Date: $to_date<br>";
    echo "<br>Records that fall within that date range:";
    
    //----------------------------------------------------------------------------------------------------//
    //				Establish Connection to Server & Database 		  	  //
    //----------------------------------------------------------------------------------------------------//
    
    	//speficy server name and database
    	$serverName = "(local)";
    	$connectionInfo = array( 'Database'=>'MyDB');
    
    	//Connect using Windows Authentication.
    	$conn = sqlsrv_connect( $serverName, $connectionInfo);
    
    	if( $conn === false )
    	{
    	  echo "Unable to connect.</br>";
    	  die( print_r( sqlsrv_errors(), true));
    	}
    
    //----------------------------------------------------------------------------------------------------//
    //			SQL Query to Retrieve the Attributes of Relevant CrimeNode Entries	  	  //
    //----------------------------------------------------------------------------------------------------//
    
    date_default_timezone_set('yourtimezonecodehere');
    
    	//declare the SQL statement that will query the database
    	//convert(varchar, getdate(), 103) puts the default format, yyyy-mm-dd into dd/mm/yyyy
    	$query = "SELECT 	id, 
    				convert(varchar, incident_start_date, 103) AS 'start_date'";
    	$query .= "FROM 	table1 ";
    	$query .= "WHERE	table1.start_date BETWEEN convert(date, '".$from_date."', 103) AND convert(date, '".$to_date."', 103)";
    
    echo "<br><br>" . $query . "<br><br>";
    
    	//execute the SQL query and return records
    	//SQLSRV driver requires array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ) 
    	//parameters in order to store number of rows... not sure why.
    	$result = sqlsrv_query($conn, $query, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
    
    
    //----------------------------------------------------------------------------------------------------//
    //				Store SQL Query Table Attribute Names 			  	  //
    //----------------------------------------------------------------------------------------------------//
    
    $field_name = array();
    foreach(sqlsrv_field_metadata($result) as $fieldMetadata)
    {
    	$counter=4;
    	foreach($fieldMetadata as $name => $value)
    	{
    		if($counter % 4 == 0)
    		{
      		array_push($field_name, $value);
    			$counter++;
    		}
    	}
    }
    
    echo "<br><br>";
    
    //----------------------------------------------------------------------------------------------------//
    //				Print Results in a Table				  	  //
    //----------------------------------------------------------------------------------------------------//
    
    echo "<table border='1'><tr>";
    $counter=0;
    while($counter < sizeof($field_name))
    {
    	echo"<th><font size=\"1\">$field_name[$counter]</th>";
    	$counter++;
    }
    echo "</tr>";
    while($row = sqlsrv_fetch_array($result))
    {
    	echo "<tr>";
    	$field_name_counter=0;
    	while($field_name_counter<sizeof($field_name))
    	{
    		echo "<td>$row[$field_name_counter]</td>";
    		$field_name_counter++;
    	}
    	echo "</tr>";
    }
    echo "</table>";
    
    
    
    ?>
    

     

     


    • Marked as answer by kevin-berry Wednesday, June 08, 2011 3:29 AM
    Wednesday, June 08, 2011 2:25 AM