Re: MySQL Invalid Date Problem - Peter Campbell - com.mysql.lists.mysql

5 messages in com.mysql.lists.mysqlRe: MySQL Invalid Date Problem

From	Sent On	Attachments
Enoch Chan	13 Dec 1999 03:15
sin...@mysql.com	13 Dec 1999 05:11
Peter Campbell	13 Dec 1999 12:21
Quentin Bennett	13 Dec 1999 13:18
Michael Widenius	27 Dec 1999 14:31

Subject:	Re: MySQL Invalid Date Problem
From:	Peter Campbell (pc...@acs.co.nz)
Date:	12/13/1999 12:21:18 PM
List:	com.mysql.lists.mysql

I have a similar date problem with queries;

The following query works as expected, getting all November transactions.

SELECT * FROM TRAN WHERE DATE >= '1999-09-01' AND DATE <= '1999-09-30'

However

SELECT * FROM TRAN WHERE DATE BETWEEN '1999-09-01' AND '1999-09-30'

ignores all transactions on the last day of the month, changing the date to an invalid '1999-09-31' works fine (or appending the time 23:59 to the datetime field as I have changed my code to do).

Note: all my date fields have a 00:00:00 time value. Mysql version = 3.23.5-alpha

Enoch Chan writes:

We found that we were able to input invalid dates into a MySQL datetime field (version 3.22.27 on Sparc Solaris 2.6) such as "Nov 31, 1999". A sample statement would be:

update user_detail set last_update_date = '1999-06-31';

Is there a way to have these types of records rejected or response with an error message?

Thanks, Enoch

Hi!

No, there is not. You will have to check them out in your program.

+----------------------------------------------------------------------+ | TcX ____ __ _____ _____ ___ == mys...@tcx.se | | /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic | | /*/ /*/ /*/ \*\_ |*| |*||*| mailto:sin...@cytanet.com.cy| | /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus | | /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____ | | ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ | | /*/ \*\ Developers Team | +----------------------------------------------------------------------+