List:General Discussion« Previous MessageNext Message »
From:Quentin Bennett Date:December 13 1999 9:19pm
Subject:RE: MySQL Invalid Date Problem
View as plain text  
The date '1999-11-30 00:00:00' is that start of 30/11/99, so your 'between'
query will exclude anything after that time - i.e. all transactions from
30/11/99 00:00:01 onwards.

Quentin

> -----Original Message-----
> From:	Peter Campbell [SMTP:pc@stripped]
> Sent:	Tuesday, December 14, 1999 9:22 AM
> To:	mysql@stripped
> Subject:	Re: MySQL Invalid Date Problem
> 
> 
> 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.
> 
> 
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread20996@stripped
> 
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.




==========================================================================
This e-mail message has been scanned and cleared by MailMarshal
MailMarshal Web Site: http://www.mailmarshal.com/
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