Professional Web Applications Themes

mysql 5.0.37....datediff... problem - MySQL

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...

  1. #1

    Default mysql 5.0.37....datediff... problem

    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
    zoilus Guest

  2. #2

    Default Re: mysql 5.0.37....datediff... problem

    zoilus wrote: 

    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.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: mysql 5.0.37....datediff... problem

    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 CHT=latin1;

    Jerry Stuckle wrote:
     
    >
    >
    > 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?
    >
    >[/ref]
    zoilus Guest

  4. #4

    Default Re: mysql 5.0.37....datediff... problem

    zoilus wrote: 
    >>
    >>
    >> 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?
    >>
    >>[/ref]
    > 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 CHT=latin1;
    >[/ref]

    (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.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: mysql 5.0.37....datediff... problem

    Thanks for picking up that error. I will give it a try.


    Jerry Stuckle wrote:
     
    > > 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 CHT=latin1;
    > >[/ref]
    >
    > (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.
    >[/ref]
    zoilus Guest

Similar Threads

  1. SQL DATEDIFF problem
    By Dinghus in forum Coldfusion Database Access
    Replies: 9
    Last Post: January 14th, 05:19 PM
  2. Datediff Problem
    By Tim::. in forum ASP Database
    Replies: 6
    Last Post: October 9th, 07:49 PM
  3. Query - Datediff problem
    By Niles Runeberg in forum Coldfusion Database Access
    Replies: 7
    Last Post: August 19th, 05:17 PM
  4. [MySQL] DateDiff function
    By Marja in forum Dreamweaver AppDev
    Replies: 2
    Last Post: July 10th, 09:47 AM
  5. Replies: 4
    Last Post: September 19th, 01:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139