|
|
Welcome to the Computer Webmaster Gaming Console Graphics Forum forums. You are currently viewing our boards as a guest which gives you limited access to view most discussions and access our other features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload content and access many other special features. Registration is fast, simple and absolutely free so please, join our community today! If you have any problems with the registration process or your account login, please contact contact us. |
| |||||||
| Database Database problems or need to ask a question? maybe something to do with sql injections or a database software question. Database topics cover MySQL, PostgreSQL, Oracle, SQL Server or anything else related to databases. |
 |
| | LinkBack | Thread Tools | Display Modes |
07-02-2007, 10:00 AM
| #1 | ||
 |
Why am I getting null value results? Anyone. select DATEDIFF(date_start,date_end) from date_test; result set null null null null data id date_start date_end 1 2004-02-00 2006-02-00 2 2004-02-00 2006-02-00 3 2006-02-00 2008-02-00 4 2004-02-00 2006-02-00 5 2006-02-00 2008-02-00 6 2004-02-00 2006-02-00 7 2006-02-00 2008-02-00 8 2004-02-00 2006-02-00 9 2006-02-00 2008-02-00 10 2004-02-00 2006-02-00 11 2006-02-00 2008-02-00 | ||
      |
| Advertisements |
|
07-02-2007, 10:00 AM
| #2 | ||
| |
zoilus wrote: > Why am I getting null value results? Anyone. > > select DATEDIFF(date_start,date_end) from date_test; > > result set > > null > null > null > null > > data > id date_start date_end > 1 2004-02-00 2006-02-00 > 2 2004-02-00 2006-02-00 > 3 2006-02-00 2008-02-00 > 4 2004-02-00 2006-02-00 > 5 2006-02-00 2008-02-00 > 6 2004-02-00 2006-02-00 > 7 2006-02-00 2008-02-00 > 8 2004-02-00 2006-02-00 > 9 2006-02-00 2008-02-00 > 10 2004-02-00 2006-02-00 > 11 2006-02-00 2008-02-00 Just a guess - but maybe date_start and date_end are not DATE types? They sure don't look like good dates. Maybe they are CHAR or VARCHAR instead? -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ================== | ||
| |
|
07-02-2007, 10:00 AM
| #3 | ||
| |
Nope, here is layout. DROP TABLE IF EXISTS `date_test`; CREATE TABLE `date_test` ( `id` int(4) unsigned zerofill NOT NULL auto_increment, `date_start` date NOT NULL default '0000-00-00', `date_end` date NOT NULL default '0000-00-00', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Jerry Stuckle wrote: > zoilus wrote: > >> Why am I getting null value results? Anyone. >> >> select DATEDIFF(date_start,date_end) from date_test; >> >> result set >> >> null >> null >> null >> null >> >> data >> id date_start date_end >> 1 2004-02-00 2006-02-00 >> 2 2004-02-00 2006-02-00 >> 3 2006-02-00 2008-02-00 >> 4 2004-02-00 2006-02-00 >> 5 2006-02-00 2008-02-00 >> 6 2004-02-00 2006-02-00 >> 7 2006-02-00 2008-02-00 >> 8 2004-02-00 2006-02-00 >> 9 2006-02-00 2008-02-00 >> 10 2004-02-00 2006-02-00 >> 11 2006-02-00 2008-02-00 > > > Just a guess - but maybe date_start and date_end are not DATE types? > They sure don't look like good dates. Maybe they are CHAR or VARCHAR > instead? > > | ||
| |
|
07-02-2007, 10:00 AM
| #4 | ||
| |
zoilus wrote: > Jerry Stuckle wrote: > >> zoilus wrote: >> >>> Why am I getting null value results? Anyone. >>> >>> select DATEDIFF(date_start,date_end) from date_test; >>> >>> result set >>> >>> null >>> null >>> null >>> null >>> >>> data >>> id date_start date_end >>> 1 2004-02-00 2006-02-00 >>> 2 2004-02-00 2006-02-00 >>> 3 2006-02-00 2008-02-00 >>> 4 2004-02-00 2006-02-00 >>> 5 2006-02-00 2008-02-00 >>> 6 2004-02-00 2006-02-00 >>> 7 2006-02-00 2008-02-00 >>> 8 2004-02-00 2006-02-00 >>> 9 2006-02-00 2008-02-00 >>> 10 2004-02-00 2006-02-00 >>> 11 2006-02-00 2008-02-00 >> >> >> Just a guess - but maybe date_start and date_end are not DATE types? >> They sure don't look like good dates. Maybe they are CHAR or VARCHAR >> instead? >> >> > Nope, here is layout. > > DROP TABLE IF EXISTS `date_test`; > CREATE TABLE `date_test` ( > `id` int(4) unsigned zerofill NOT NULL auto_increment, > `date_start` date NOT NULL default '0000-00-00', > `date_end` date NOT NULL default '0000-00-00', > PRIMARY KEY (`id`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > (Top posting fixed) Then how do you get the invalid dates in there? 2006-02-00 is not a valid date. Use valid dates and you'll get valid results. P.S. Please don't top post. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ================== | ||
| |
|
07-02-2007, 10:00 AM
| #5 | ||
| |
Thanks for picking up that error. I will give it a try. Jerry Stuckle wrote: > zoilus wrote: > >> Jerry Stuckle wrote: >> >>> zoilus wrote: >>> >>>> Why am I getting null value results? Anyone. >>>> >>>> select DATEDIFF(date_start,date_end) from date_test; >>>> >>>> result set >>>> >>>> null >>>> null >>>> null >>>> null >>>> >>>> data >>>> id date_start date_end >>>> 1 2004-02-00 2006-02-00 >>>> 2 2004-02-00 2006-02-00 >>>> 3 2006-02-00 2008-02-00 >>>> 4 2004-02-00 2006-02-00 >>>> 5 2006-02-00 2008-02-00 >>>> 6 2004-02-00 2006-02-00 >>>> 7 2006-02-00 2008-02-00 >>>> 8 2004-02-00 2006-02-00 >>>> 9 2006-02-00 2008-02-00 >>>> 10 2004-02-00 2006-02-00 >>>> 11 2006-02-00 2008-02-00 >>> >>> >>> >>> Just a guess - but maybe date_start and date_end are not DATE types? >>> They sure don't look like good dates. Maybe they are CHAR or VARCHAR >>> instead? >>> >>> > > Nope, here is layout. > > > > DROP TABLE IF EXISTS `date_test`; > > CREATE TABLE `date_test` ( > > `id` int(4) unsigned zerofill NOT NULL auto_increment, > > `date_start` date NOT NULL default '0000-00-00', > > `date_end` date NOT NULL default '0000-00-00', > > PRIMARY KEY (`id`) > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > > > > (Top posting fixed) > > Then how do you get the invalid dates in there? 2006-02-00 is not a > valid date. > > Use valid dates and you'll get valid results. > > P.S. Please don't top post. > | ||
| |
| Featured Websites | ||||
|
|
| Tags: 5037datediff, mysql, problem |
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | |
| Display Modes | |
| |
Similar Threads | ||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Need Help, problem with OSX, MySQL, etc. | Jerry McEwen | Database | 4 | 07-01-2007 8:34 PM |
| problem with mysql.sock | Ben | Database | 4 | 07-01-2007 5:36 PM |
| Problem using OLE DB 3 with MySQL 5 | Stefano Del Furia | Database | 0 | 07-01-2007 5:30 PM |
| mysql problem | edgy | Database | 6 | 06-09-2007 11:26 PM |
| Problem with MySQL and ODBC | André | Database | 2 | 05-31-2007 7:46 PM |
| Featured Websites | ||||
|
mysql 5.0.37....datediff... problem 
PacMan
Linear Mode
