Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


B.5.5.2 DATE カラムの使用に関する問題

DATE 値の形式は 'YYYY-MM-DD' です。標準 SQL に従うと、ほかの形式は許可されません。UPDATE の式および SELECT ステートメントの WHERE 句では、この形式を使用してください。例:

SELECT * FROM t1 WHERE date >= '2003-05-05';

利便性のため、日付が数値のコンテキストで使用されている場合、MySQL は日付を数値に自動的に変換します (その逆にも変換されます)。また、MySQL は、更新時、および日付を DATEDATETIME、または TIMESTAMP カラムと比較する WHERE 句で、緩やかな文字列形式を許可します。緩やかな形式とは、各部分の区切り文字として句読点文字を使用できることを意味します。たとえば、'2004-08-15''2004#08#15' は同等です。MySQL は、日付として解釈できる場合、区切り文字が含まれていない文字列 ('20040815' など) も変換できます。

<<==>=>、または BETWEEN 演算子を使用して、DATETIMEDATETIME、または TIMESTAMP を定数文字列と比較する場合、通常、MySQL はより速く比較するために (および緩やかな文字列チェックのため) 文字列を内部長整数に変換します。ただし、この変換には次の例外があります。

  • 2 つのカラムを比較する場合

  • DATETIMEDATETIME、または TIMESTAMP カラムと式を比較する場合

  • 上記で一覧表示した比較方法以外の比較方法を使用する場合 (INSTRCMP() など)。

これらの例外の場合、比較はオブジェクトを文字列に変換して文字列比較を実行することによって行われます。

安全に処理を行うには、時間値と文字列を比較する場合、文字列が文字列として比較されると想定し、適切な文字列関数を使用します。

特殊なゼロ日付 '0000-00-00' は、'0000-00-00'. として格納および取得できます。'0000-00-00' 日付が Connector/ODBC を介して使用される場合、ODBC はそのような日付を処理できないため、NULL に自動的に変換されます。

MySQL が前述の変換を実行するため、次のステートメントは動作します (idateDATE カラムであると想定しています)。

INSERT INTO t1 (idate) VALUES (19970505);
INSERT INTO t1 (idate) VALUES ('19970505');
INSERT INTO t1 (idate) VALUES ('97-05-05');
INSERT INTO t1 (idate) VALUES ('1997.05.05');
INSERT INTO t1 (idate) VALUES ('1997 05 05');
INSERT INTO t1 (idate) VALUES ('0000-00-00');

SELECT idate FROM t1 WHERE idate >= '1997-05-05';
SELECT idate FROM t1 WHERE idate >= 19970505;
SELECT MOD(idate,100) FROM t1 WHERE idate >= 19970505;
SELECT idate FROM t1 WHERE idate >= '19970505';

ただし、次のステートメントは動作しません。

SELECT idate FROM t1 WHERE STRCMP(idate,'20030505')=0;

STRCMP() は文字列関数であり、idate'YYYY-MM-DD' という形式の文字列に変換して、文字列比較を実行します。'20030505' は日付 '2003-05-05' に変換されずに、日付比較が実行されます。

ALLOW_INVALID_DATES SQL モードを有効にしている場合、MySQL は限定的なチェックのみが行われた日付を格納することを許可します。MySQL は、日が 1 から 31 までの範囲内にあり、月が 1 から 12 までの範囲内にあることのみを要求します。これにより、Web アプリケーションで年、月、および日を 3 つの別個のフィールドで取得して、ユーザーが入力したとおりに格納する (日付検証なしで) 場合に、MySQL が非常に便利になります。

MySQL は、日または月と日がゼロである日付の格納を許可します。これは、生年月日を DATE カラムに格納するが、その日付の一部のみがわかっている場合に便利です。日付でゼロの月または日の部分を無効にするには、NO_ZERO_IN_DATE SQL モードを有効にします。

MySQL では、ダミーの日付として '0000-00-00'ゼロの値を格納できます。これは、NULL 値を使用するよりも便利な場合があります。DATE カラムに格納される日付を妥当な値に変換できない場合、MySQL は '0000-00-00' を格納します。'0000-00-00' を無効にするには、NO_ZERO_DATE SQL モードを有効にします。

MySQL がすべての日付をチェックして、有効な日付のみを受け入れるようにするには (IGNORE によってオーバーライドされないかぎり)、sql_mode システム変数に "NO_ZERO_IN_DATE,NO_ZERO_DATE" を設定します。


User Comments
  Posted by Jörg Höhle on January 9, 2003
It seems that the 0000-00-00 to NULL conversion also affects JDBC (mysql connectorJ 3.0.3-beta), not only ODBC.
  Posted by Rory Sellers on January 27, 2003
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)!
  Posted by Nick Springer on April 26, 2003
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
%>
  Posted by Jorge Solis on April 26, 2003
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
  Posted by mark roth on June 22, 2004
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...
  Posted by Michael Last on June 30, 2005
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
  Posted by Jonathan Woods on November 9, 2005
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 '&amp;'.

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

Jon
  Posted by Komang Arthayasa on October 20, 2006
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

  Posted by Marcus Matos on January 8, 2008
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.

Sign Up Login You must be logged in to post a comment.