List:General Discussion« Previous MessageNext Message »
From:Peter Campbell Date:December 13 1999 8:21pm
Subject:Re: MySQL Invalid Date Problem
View as plain text  
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.
> 
> Sinisa
> 
> +----------------------------------------------------------------------+
> | TcX  ____  __     _____   _____  ___     ==  mysql@stripped            |
> |     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sinisa Milivojevic          |
> |    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sinisa@stripped|
> |   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Larnaka, Cyprus             |
> |  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                             |
> |  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                            |
> |             /*/             \*\                Developers Team       |
> +----------------------------------------------------------------------+
> 



Peter Campbell
http://www.fastbase.co.nz
Phone NZ 04 385 2230, Fax 04 385 2224.

Thread
MySQL Invalid Date ProblemEnoch Chan13 Dec
  • Re: MySQL Invalid Date Problemsinisa13 Dec
    • Re: MySQL Invalid Date ProblemPeter Campbell13 Dec
      • Re: MySQL Invalid Date ProblemMichael Widenius28 Dec
RE: MySQL Invalid Date ProblemQuentin Bennett14 Dec