>Description: I have found some inconsistencies (one may call them bugs ;-) in the current MySQL 3.23 version. I tested this on Linux and Solaris 2.8 so this should not be platform specific... The result differs, if one uses the constant value "0000-00-00" or if one uses a column which has the value "0000-00-00". The following functions are affected: DAYOFMONTH, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, YEARWEEK, TO_DAYS, DATE_FORMAT and EXTRACT. Not tested: DATE_ADD, DATE_SUB, ADDDATE and SUBDATE.
>How-To-Repeat: CREATE TABLE xx ( Gueltig DATE NOT NULL ); INSERT INTO xx VALUES ( "0000-00-00" ); SELECT DAYOFYEAR("0000-00-00"),Gueltig,DAYOFYEAR(Gueltig) FROM xx; +-------------------------+------------+--------------------+ | DAYOFYEAR("0000-00-00") | Gueltig | DAYOFYEAR(Gueltig) | +-------------------------+------------+--------------------+ | NULL | 0000-00-00 | -730484 | +-------------------------+------------+--------------------+ 1 row in set (0.00 sec) SELECT DAYOFMONTH("0000-00-00"),Gueltig,DAYOFMONTH(Gueltig) FROM xx; +--------------------------+------------+---------------------+ | DAYOFMONTH("0000-00-00") | Gueltig | DAYOFMONTH(Gueltig) | +--------------------------+------------+---------------------+ | NULL | 0000-00-00 | 0 | +--------------------------+------------+---------------------+ 1 row in set (0.00 sec) and so on... >Fix: don't know :-( except SELECT IF(Gueltig="0000-00-00",NULL,DAYOFYEAR(Gueltig)) FROM xx; but this would mean to check all programs... >Submitter-Id: <submitter ID> >Originator: >Organization: >MySQL support: none >Synopsis: bugs / inconsistencies with date functions >Severity: non-critical >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-3.23.43 (Source distribution) >Server: /usr/local/bin/mysqladmin Ver 8.21 Distrib 3.23.43, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.43-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 11 days 21 hours 4 min 9 sec Threads: 7 Questions: 18875287 Slow queries: 3198 Opens: 63795 Flush tables: 992 Open tables: 63 Queries per second avg: 18.393 >Environment: System: Linux cleopatra 2.2.19 #7 Sat Oct 6 02:37:48 CEST 2001 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: -rwxr-xr-x 1 root root 4060896 Jan 17 2001 /lib/libc.so.6 -rw-r--r-- 1 root root 19186278 Jan 17 2001 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Jan 17 2001 /usr/lib/libc.so Configure command: ./configure --without-bench --without-docs Perl: This is perl, version 5.005_03 built for i586-linux --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php