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 <[email protected]> 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 <[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]> 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]. >>> To unsubscribe from this group, send email to >>> [email protected]<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. >> >> >> -- >> 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]<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]<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.
