1. Helge Tjelta
  2. Other Discussions
  3. Montag, Januar 09 2017, 09:21 PM
  4.  Abonnieren via E-Mail
Hi, I'm doing a Datediff, but how can I fix the wrong year I get...

select navn, doeddato, foedt_dato, DATEDIFF( '08.01.2017','15.01.1942', 'year')

should not give 75 as year...
but if I use 'day' and divide with 365.25(for leap-years) then I get 74.9815 and that is 74 years and not 75 years...

Floor and trunc does not fix it with years... is days the way to go? and how can I check enough leap-years... ???
Kommentar
There are no comments made yet.
Helge Tjelta Akzeptierte Antwort
0
Votes
Undo
leap year is not just each 4 years... have a look at this from wikipedia:

if (year is not divisible by 4) then (it is a common year)
else if (year is not divisible by 100) then (it is a leap year)
else if (year is not divisible by 400) then (it is a common year)
else (it is a leap year)
Kommentar
There are no comments made yet.
Helge Tjelta Akzeptierte Antwort
0
Votes
Undo
wll, if this is how we gonna do it, we have to face that certain datediff will be wrong..

What if the diff is 9 years and you can either have 2 or 3 leap years, then the diff might be one day off and thus giving a wrong diff....

The point being; divide a sum on 365.25 is not accurate, because some settings will have more than 1/4 years of leap years... so how can we fix this... is the a way to get number of leap years in a diff ?
Kommentar
There are no comments made yet.
Scott Akzeptierte Antwort
0
Votes
Undo
How about:


floor((datediff('08/01/2017','01/15/1942', 'second')/86400)/365.25)


Solve for seconds (86400 in one day) and then divide for days (365.25 for leap year) and use the FLOOR function to round down.

Works for me. 75
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
0
Votes
Undo
Hi Helge,

first answer is: this is not our algs, we using ICU library.
Kommentar
There are no comments made yet.
  • Seite :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 0 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories
Omegabundle
  1. 0 subcategories