The format of a DATE
value is
'YYYY-MM-DD'
. According to standard SQL, no
other format is allowed. You should use this format in
UPDATE
expressions and in the
WHERE
clause of
SELECT
statements. For example:
mysql> SELECT * FROM tbl_name
WHERE date >= '2003-05-05';
As a convenience, MySQL automatically converts a date to a
number if the date is used in a numeric context (and vice
versa). It is also smart enough to allow a
“relaxed” string form when updating and in a
WHERE
clause that compares a date to a
TIMESTAMP
,
DATE
, or
DATETIME
column.
(“Relaxed form” means that any punctuation
character may be used as the separator between parts. For
example, '2004-08-15'
and
'2004#08#15'
are equivalent.) MySQL can
also convert a string containing no separators (such as
'20040815'
), provided it makes sense as a
date.
When you compare a DATE
,
TIME
,
DATETIME
, or
TIMESTAMP
to a constant string
with the <
, <=
,
=
, >=
,
>
, or BETWEEN
operators, MySQL normally converts the string to an internal
long integer for faster comparison (and also for a bit more
“relaxed” string checking). However, this
conversion is subject to the following exceptions:
For these exceptional cases, the comparison is done by converting the objects to strings and performing a string comparison.
To keep things safe, assume that strings are compared as strings and use the appropriate string functions if you want to compare a temporal value to a string.
The special date '0000-00-00'
can be stored
and retrieved as '0000-00-00'.
When using a
'0000-00-00'
date through MyODBC, it is
automatically converted to NULL
in MyODBC
2.50.12 and above, because ODBC can't handle this kind of
date.
Because MySQL performs the conversions described above, the following statements work:
mysql>INSERT INTO
mysql>tbl_name
(idate) VALUES (19970505);INSERT INTO
mysql>tbl_name
(idate) VALUES ('19970505');INSERT INTO
mysql>tbl_name
(idate) VALUES ('97-05-05');INSERT INTO
mysql>tbl_name
(idate) VALUES ('1997.05.05');INSERT INTO
mysql>tbl_name
(idate) VALUES ('1997 05 05');INSERT INTO
mysql>tbl_name
(idate) VALUES ('0000-00-00');SELECT idate FROM
mysql>tbl_name
WHERE idate >= '1997-05-05';SELECT idate FROM
mysql>tbl_name
WHERE idate >= 19970505;SELECT MOD(idate,100) FROM
mysql>tbl_name
WHERE idate >= 19970505;SELECT idate FROM
tbl_name
WHERE idate >= '19970505';
However, the following does not work:
mysql> SELECT idate FROM tbl_name
WHERE STRCMP(idate,'20030505')=0;
STRCMP()
is a string function,
so it converts idate
to a string in
'YYYY-MM-DD'
format and performs a string
comparison. It does not convert '20030505'
to the date '2003-05-05'
and perform a date
comparison.
If the date cannot be converted to any reasonable value, a
0
is stored in the
DATE
column, which is retrieved
as '0000-00-00'
. This is both a speed and a
convenience issue. We believe that the database server's
responsibility is to retrieve the same date you stored (even
if the data was not logically correct in all cases). We think
it is up to the application and not the server to check the
dates.
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.