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 <[email protected]
> <mailto:[email protected]>> 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 <[email protected]
>> <mailto:[email protected]>> 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
>> [email protected]
>> <mailto:[email protected]>.
>> To unsubscribe from this group, send email to
>> [email protected]
>> <mailto:h2-database%[email protected]>.
>> 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 [email protected]
>> <mailto:[email protected]>.
>> To unsubscribe from this group, send email to
>> [email protected]
>> <mailto:[email protected]>.
>> 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 [email protected]
> <mailto:[email protected]>.
> To unsubscribe from this group, send email to
> [email protected]
> <mailto:h2-database%[email protected]>.
> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
