To properly put the date and time into a MySQL date field, you need to put it into the format of:

yyyy-mm-dd hh:mm:ss


Here is some code to do this in classic ASP for input into a MySQL table:

Dim testdate, testmonth, testday, ISODate
testdate = Request.Form("frmField5")
testmonth = Right(Month(testdate),2)
testday = Right(Day(testdate),2)
ISODate = Year(testdate) & "-" & testmonth & "-" & testday & " " & hour(testdate) & ":" & minute(testdate) & ":" & second(testdate)


The ISODate variable is in the correct format to insert in a MySQL UPDATE or INSERT SQL statement. Below is an example SQL string to UPDATE a date field in a MySQL table:

UPDATE myTable SET thisdate = 'ISODate' WHERE myID = 'thisID'


Digg

Stumble Upon

Del.icio.us

Buzz

Maintaining databases can be a chore, but knowing the appropriate SQL to do the job can save hours of time. Here is a tidbit regarding changing a select string of text within a specific field of a table in a MySQL database. You can edit 1000s of records with this one query:
UPDATE table SET field = REPLACE(field, 'text to be replaced', 'replacement text');

In this example, the table name is “table”, the field to find the text in is named “field”, the rest should be self explanatory.

Digg

Stumble Upon

Del.icio.us

Buzz

MySQL time and date functions are well documented (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html), but the applications for specific needs are not. I would like to share a tidbit about comparing two database date entries, or one entry to the current time and then comparing the difference.

The application is for an auction website, where I am comparing the current time to either the ending time date or the beginning time date to determine how many hours the current time is from either of those timestamps.

My PHP code sets a MySQL variable to insert into the SQL statement to compare the end or beginning timestamp.


//In PHP, depending on the search query, I set the SQL to compare the beginning timestamp or the ending timestamp.

if($cat==1){$catsql="TIMESTAMPDIFF(HOUR,StartTime,Now()) < '24'";}
elseif($cat==2){$catsql="TIMESTAMPDIFF(HOUR,Now(),EndTime) < '24'";}
else{$catsql="";}

In the SQL code, I am finding the difference in Hours from the current time Now() to either the EndTime or the StartTime timestamps. If less then 24 hours, it allows a condition to occur in the SQL statement to select specific records meeting that condition.

Keep in mind that the time date format is important and should be in the following format for the above MySQL statements to work:


$currenttime = date("Y-m-d H:i:s"); //PHP timestamp consitant with MySQL


Digg

Stumble Upon

Del.icio.us

Buzz