Passing a date to display database

[rtipton]

I am trying to pass date to a database so that I can display the results.
Here is the HTML that I am sending
Location_results.php?Startdate=01%2F01%2F08&Enddate=07%2F31%2F08&Area=INTERAREA&button=Submit

It is only looking at the month and not the whole string.

In the database the date is like this 01/20/08.

below is the search that I am doing in MYSQL

$colname_Get_Total_Region = "-1";
if (isset($_GET['Startdate'])) {
  $colname_Get_Total_Region = $_GET['Startdate'];
}
$colname2_Get_Total_Region = "-1";
if (isset($_GET['Enddate'])) {
  $colname2_Get_Total_Region = $_GET['Enddate'];
}
$colname1_Get_Total_Region = "-1";
if (isset($_GET['Area'])) {
  $colname1_Get_Total_Region = $_GET['Area'];
}
mysql_select_db($database_TechSupport, $TechSupport);
$query_Get_Total_Region = sprintf("SELECT * FROM Chronics_report WHERE Ticket_Created_Date >=  %s AND Chronics_report.Ticket_Created_Date <= %s AND Chronics_report.Area = %s ", GetSQLValueString($colname_Get_Total_Region, "text"),GetSQLValueString($colname2_Get_Total_Region, "text"),GetSQLValueString($colname1_Get_Total_Region, "text"));
$Get_Total_Region = mysql_query($query_Get_Total_Region, $TechSupport) or die(mysql_error());
$row_Get_Total_Region = mysql_fetch_assoc($Get_Total_Region);
$totalRows_Get_Total_Region = mysql_num_rows($Get_Total_Region);

[devinemke]

what data type is Ticket_Created_Date? your post would suggest that it is a string type. don't store dates as strings, store dates as dates. look at mySQL Date and Time types.

[rtipton]

Ticket_create_date is the column name where the date is stored in the database. I am trying to search on that column to display the data that matches the dates that I choose

[devinemke]

my question still stands: what data type is Ticket_Created_Date?

[rtipton]

the data that is in the field is mm/dd/yy (ex. 01/31/09) and I have the type as a text

[devinemke]

rtipton;10920047 wrote:

the data that is in the field is 01/31/09 and I have the type as a text

...that is the problem. (as a stated ealier) you need to use one of mySQL's Date and Time types.

[rtipton]

How do i set up the column so that the format stays the same as mm/dd/yy. When i change the field type to date I get yyyy-mm-dd. So am I going to have to change all the data that I have in the database to the yyyy-mm-dd format.

Sorry for asking these questions I am still new on how to do MYSQL

[devinemke]

mySQL's native date format is YYYY-MM-DD. you can use many of mySQL's Date & Time functions (such as DATE_FORMAT) to format it any way you want. to convert your existing strings to proper dates you could use STR_TO_DATE.

[rtipton]

Thank you for your help. I got one more question. How would i use the STR_TO_DATE to replace the date that I have know in the database. I need help with the sentax. I used phpadmin to create the database. So how would i get str_to_date to change the date that I have already have in the database

[devinemke]

create a new DATE column, then...

UPDATE Chronics_report SET new_Ticket_Created_Date = (SELECT STR_TO_DATE(Ticket_Created_Date, '%m/%d/%y'))

[rtipton]

Thank you very much It worked