## Gelöst Datediff shows wrong year, or ???

1. Helge Tjelta
2. Other Discussions
3. Montag, Januar 09 2017, 09:21 PM
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
Antworten (4)
Helge Tjelta Akzeptierte Antwort
0
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
Helge Tjelta Akzeptierte Antwort
0
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
Scott Akzeptierte Antwort
0
Undo

``````
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
Ruslan Zasukhin Akzeptierte Antwort
0
Undo
Hi Helge,

first answer is: this is not our algs, we using ICU library.
Kommentar
• 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