User Comments

It seems that the 0000-00-00 to NULL conversion also affects JDBC (mysql connectorJ 3.0.3-beta), not only ODBC.

I'm surprised nobody has mentioned DATE_FORMAT(). After all, the problem for many folks isn't how the data is stored, but how it is displayed, and reformatting the display through the database at SELECT-time is almost always easier than using the application (e.g. PHP or whatever)!

Just tell MySQL to format the date when you do your select statement and then it will show up nicely formatted (using DATE_FORMAT) ...

EXAMPLE
select DATE_FORMAT(yourcolumnname, '%M %e %Y') from yourtablename;

Here is a simple ASP/VBScript function you can use to convert a VBScipt date variable in a MySQL compatible string. Place the function in an APS doc and anytime you need a MySQL date use "convertDate(your_date)"

<%
function convertDate(varDate)
if day(varDate) < 10 then
dd = "0" & day(varDate)
else
dd = day(varDate)
end if

if month(varDate) < 10 then
mm = "0" & month(varDate)
else
mm = month(varDate)
end if

convertDate = year(varDate) & mm & dd
end function
%>

Select a Date between dates

mySQL supports the between operator. SELECT * FROM Appointments WHERE
Moment BETWEEN StartTime AND EndTime ORDER BY StartTime;

Anyway, you need to use 20030427, not 2003-04-27

Jorge Solis
flash-db.com

In addition to what Amie said...

This is the method I have used and it works well:

SELECT DATE_FORMAT( `row_for_date` , '%c-%e-%y' ) AS revised_date, TIME_FORMAT( `row_for_time` , '%h:%i %p' ) AS revised_time FROM table_name

Then throw it into a while loop like so:

while($row = mysql_fetch_assoc($query_above)) {
echo 'The date is : '.$row['revised_date'].'<br>';
echo 'The time is : '.$row['revised_time'];
}

This, of course, needs to have the values of the DATE and TIME in the database already. In addition, you can add a WHERE to the end of the query to limit your search.

-Steveo

After much hair-tearing, I finally found how to do timestamp *comparisons*: the number which the timestamp column is being compared to *must* be valid numeric time. 20040622585858 is fine, but 20040622685800 (note the 68th minute) results in what appears to be a non-comparison, and everything is selected.

I would have expected a straight numeric comparision, but nooooo...

Here is an ASP function for changing DATE and TIME for mysql odbc:

function mysqldate(dt)
'dt should be a date time string -michaeldarkblue@yahoo.com
if isdate(dt) then
mysqldate = year(dt) & "-" & month(dt) & "-" & day(dt) & " " & hour(dt) & ":" & minute(dt) & ":" & second(dt)
else
mysqldate = 0
end if
end function

For anyone using JDBC and experiencing problems with NULL date columns (e.g. J�rg H�hle above): you can ask MySQL nicely to return NULLs for date columns holding 0000-00-00 - the sensible behaviour, imho. To do this, set the property zeroDateTimeBehaviour when creating the JDBC Connection. It seems it is best to do this by specifying the property as part of a JDBC URL, so:

jdbc:mysql://{host}:{port}/{dbname}?user={user}&password={password}&zeroDateTimeBehavior=convertToNull

where you should use the appropriate values for the stuff in curly braces.

NOTE: if you're specifying this URL in an XML file, e.g. for the web.xml context descriptor of a servlet-based web app, replace '&' with '&'.

For more details and a better explanation, Google for zeroDateTimeBehavior.

Jon

Here are some usefull Date/Time Conversion in ASP

'***** function to COnvert date to mySQL accepted format

Function FormatMySQLDate(sdate,booltime)
FormatMySQLDate=year(sdate)&"-"&month(sdate)&"-"&day(sdate)
if booltime then
FormatMySQLDate=FormatMySQLDate&" "&formatDatetime(sdate,4)
end if
End Function

'***** toConvert Date To NUmeric or the opposite
'***** for comparing date

Function DateToNum(sdate)
DateToNum=year(sdate) & right("00"&month(sdate),2) & right("00"&day(sdate),2)
End Function

Function NumToDate(sdate)
NumToDate=left(sdate,4) & "/" & mid(sdate,5,2) & "/" & right(sdate,2)
End Function

NOTE:
sDate Must Be a date/time variable value, booltime mean is the time format included

No ready made function is provided for validate date

This function work
(you can take what ever size you want in varchar(1-1024) )

CREATE FUNCTION IsDate (sIn varchar(1024)) RETURNS INT
BEGIN
declare tp int;

if length(date(sIn)) is not null then
set tp = 0;
else
set tp = 1;
end if;
RETURN tp;
END
If you find any bug for this please post it here as this is not complete soluction as date respond are not known
I will try to solve this

Important: It should be known that MySQL >= 5.0.42 silently changes the behavior of comparing a DATE column to NOW().

See: http://bugs.mysql.com/bug.php?id=28929

This breaks many things since now queries using WHERE datecol = NOW() will return NULL where previously it would return results.

Use CURDATE() instead. I'm having to go back through years of code to fix this.

I use this function on testing if a value is a correct date;

CREATE FUNCTION isdate(EVAL_DATE VARCHAR(1024))
RETURNS tinyint(1)
BEGIN
RETURN NOT(ISNULL(DATEDIFF(CURRENT_DATE,EVAL_DATE)));
END;

the difference between a date and a not-date value is always a null value, so you have to test only for a null result and, as all the is... functions, return 1 if the tested value is a date and 0 in the other case.

the last implementation of isDate yields (1) for the obviously invalid date.
SELECT isDate('2010-02-28 eqw');

jack

Add your own comment.