Ahhhh -- now I understand. Tragically, I'm going on holiday for a week, so I
can't look at it!
Cheers
Kerry
On Sat, Jun 12, 2010 at 5:55 AM, Dario Fassi <dfa...@gmail.com> wrote:
> ok, I sorry my dirty cut/paste.
> This is the case:
>
> set @bird='1990-06-11';
> set @curday='2010-06-10';
> select datediff('YEAR',@bird,@curday) as y ,datediff('MONTH',@bird,@curday)
> as m , datediff('DAY',@bird,@curday) as d,
> datediff('MONTH',@bird,@curday)/12 as yy ;
> set @curday='2010-06-11';
> select datediff('YEAR',@bird,@curday) as y ,datediff('MONTH',@bird,@curday)
> as m , datediff('DAY',@bird,@curday) as d,
> datediff('MONTH',@bird,@curday)/12 as yy ;
> set @curday='2010-06-12';
> select datediff('YEAR',@bird,@curday) as y ,datediff('MONTH',@bird,@curday)
> as m , datediff('DAY',@bird,@curday) as d,
> datediff('MONTH',@bird,@curday)/12 as yy ;
>
> Results;
>
> Born Date Cur Date Y M D M/12
> ---------- ---------- -- --- ---- -----
> 1990-06-11 2010-06-10 20 240 7304 20
> 1990-06-11 2010-06-11 20 240 7305 20
> 1990-06-11 2010-06-12 20 240 7306 20
>
> The problem is that ONE DAY BEFORE, THE SAME DAY, AND ONE DAY AFTER for
> some date in another year (say birthday) you have the same value in years
> difference.
>
> I think the result shoud be:
>
> Born Date Cur Date Y M D M/12
> ---------- ---------- -- --- ---- -----
> 1990-06-11 2010-06-10 19 239 7304 19
> 1990-06-11 2010-06-11 20 240 7305 20
> 1990-06-11 2010-06-12 20 240 7306 20
>
>
>
> The substration operator on DB2 does something much more useful:
>
> values
> cast( '2010-06-10' as date) - cast( '1990-06-11' as date),
> cast( '2010-06-11' as date) - cast( '1990-06-11' as date),
> cast( '2010-06-12' as date) - cast( '1990-06-11' as date);
>
> ---------------------------------------------------------------------------------------------------------
> 1
> ----------
> 191129, Mean 19 years , 11 months, 29 days
> 200000, Mean 20 years , 0 months, 0 days
> 200001, Mean 20 years , 0 months, 1 days
> ----------
>
>
>
> Now it's clearly defined for you ?
>
> regards,
> Dario.
>
>
>
> El 11/06/10 02:18, Kerry Sainsbury escribió:
>
> We're an international group of people headed by a German -- I think you're
> asking too much!
>
> On Fri, Jun 11, 2010 at 3:00 PM, Rami Ojares <rami.oja...@gmail.com>wrote:
>
>> Grammatically correct english would be nice too...
>>
>> - rami
>>
>>
>> On 11.6.2010 4:58, Kerry Sainsbury wrote:
>>
>> Hi Dario,
>>
>> Could you clearly define what it is that you think is wrong? What are you
>> expecting?
>>
>> That said, I think there's a problem with your SQL -- your second and
>> third SELECT statements use "current_date" rather than "@curday", which I
>> suspect is what you really want to use :-)
>>
>> Cheers
>> Kerry
>>
>>
>> On Thu, Jun 10, 2010 at 11:34 AM, Dario Fassi <dfa...@gmail.com> wrote:
>>
>>> hi,
>>> I have found some curious results from DATEDIFF builtin function.
>>>
>>> As this:
>>>
>>> set @bird='1990-06-11';
>>> set @curday='2010-06-10';
>>> select datediff('YEAR',@bird,@curday) as y ,
>>> datediff('MONTH',@bird,@curday) as m , datediff('DAY',@bird,@curday) as
>>> d, datediff('MONTH',@bird,@curday)/12 as yy ;
>>> set @curday='2010-06-11';
>>> select datediff('YEAR',@bird,current_date) as y ,
>>> datediff('MONTH',@bird,current_date) as m ,
>>> datediff('DAY',@bird,current_date) as d,
>>> datediff('MONTH',@bird,current_date)/12 as yy ;
>>> set @curday='2010-06-12';
>>> select datediff('YEAR',@bird,current_date) as y ,
>>> datediff('MONTH',@bird,current_date) as m ,
>>> datediff('DAY',@bird,current_date) as d,
>>> datediff('MONTH',@bird,current_date)/12 as yy ;
>>>
>>> Results;
>>>
>>> 20 240 7304 20
>>> 20 240 7303 20
>>> 20 240 7303 20
>>>
>>>
>>> I missed something or this is wrong?
>>>
>>> Regards,
>>> Dario
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "H2 Database" group.
>>> To post to this group, send email to h2-datab...@googlegroups.com.
>>> To unsubscribe from this group, send email to
>>> h2-database+unsubscr...@googlegroups.com<h2-database%2bunsubscr...@googlegroups.com>
>>> .
>>> For more options, visit this group at
>>> http://groups.google.com/group/h2-database?hl=en.
>>>
>>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To post to this group, send email to h2-datab...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> h2-database+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/h2-database?hl=en.
>>
>>
>> --
>> You received this message because you are subscribed to the Google
>> Groups "H2 Database" group.
>> To post to this group, send email to h2-datab...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> h2-database+unsubscr...@googlegroups.com<h2-database%2bunsubscr...@googlegroups.com>
>> .
>> For more options, visit this group at
>> http://groups.google.com/group/h2-database?hl=en.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to h2-datab...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>
> --
> Saludos,
> Dario
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to h2-datab...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database+unsubscr...@googlegroups.com<h2-database%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
