Calculer la date exacte de la différence dans les années à l'aide de SQL

Je reçois des rapports dans lesquels les données sont ETL à la base de données automatiquement. Je extraire et transformer des données à charger quelque part d'autre. Une chose dont j'ai besoin est un DATEDIFF mais l'année doit être exact (c'est à dire, de 4,6 ans au lieu de l'arrondissement jusqu'à cinq ans.

Voici mon script:

select *, DATEDIFF (yy, Begin_date, GETDATE()) AS 'Age in Years'
from Report_Stage;

La 'Age_In_Years' colonne est arrondi. Comment puis-je obtenir la date exacte dans les années?

Si vous avez juste besoin d'un chiffre significatif, essayez DATEDIFF (jj.. et diviser par 365. Ce ne sera pas le facteur dans les années bissextiles.
que ne ferait que rendre par comme ,1 +-.1-elle correcte?
il est toujours arrondi quand je le fais de cette façon
Essayez de diviser par 365.0 au lieu de 365, qui permet d'arrêter l'implicite exprimées en nombre entier.
Qui a fait le tour. Merci.

OriginalL'auteur Squ1rr3lz | 2014-04-17

  1. 10

    Avez-vous essayé de faire la différence en mois au lieu de cela, puis à calculer les années de cette façon? Par exemple, 30 mois /12 serait de 2,5 ans.

    Edit: Cette requête SQL contient plusieurs approches pour calculer la date de différence:

    SELECT CONVERT(date, GetDate() - 912) AS calcDate
      ,DATEDIFF(DAY, GetDate() - 912, GetDate()) diffDays
      ,DATEDIFF(DAY, GetDate() - 912, GetDate()) / 365.0 diffDaysCalc
      ,DATEDIFF(MONTH, GetDate() - 912, GetDate()) diffMonths
      ,DATEDIFF(MONTH, GetDate() - 912, GetDate()) / 12.0 diffMonthsCalc
      ,DATEDIFF(YEAR, GetDate() - 912, GetDate()) diffYears
    La colonne est toujours arrondi
    ainsi fait-il répondre à votre question ou pas? Votre acceptation implique une chose, mais votre commentaire l'autre. Il est probable que tronquer en raison de la mathématique être effectuées uniquement comme des entiers (c'est à dire, il devrait être / 12.0 à force de mathématiques à virgule flottante, comme dans les commentaires sur votre question). @Cherchait - vous devez être l'ajout de code correspondant à des réponses comme ceci, ou cela devrait être fermé.
    Ajout d'un code exemple de réponse originale à cette question.
    il a répondu à ma question.
    Il y a un problème d'arrondi avec DATEDIFF(MONTH, @start, @end) / 12.0 lorsque les deux dates sont sur le même mois et le jour de départ est plus grand.

    OriginalL'auteur FumblesWithCode

  2. 12

    Tous datediff() n'est calculer le nombre de période de frontières franchies entre les dates. Par exemple

    datediff(yy,'31 Dec 2013','1 Jan 2014')

    renvoie 1.

    Vous obtiendrez un résultat plus précis, si vous calculez la différence entre les deux dates en jours et la diviser par la longueur moyenne d'une année civile, jours, plus de 400 ans (365.2425):

    datediff(day,{start-date},{end-date},) / 365.2425

    Par exemple,

    select datediff(day,'1 Jan 2000' ,'18 April 2014') / 365.2425

    retour 14.29461248 — il à la précision souhaitée.

    Je pense que c'est vraiment ce qu'il se passe. Traverser les frontières est une chose différente de l'arrondissement, même si elle peut produire des résultats qui ressemblent à des arrondis.

    OriginalL'auteur Nicholas Carey

  3. 2

    Je pense que la division par 365.2425 n'est pas une bonne façon de le faire. Pas de division peut à présent complètement avec précision (à l'aide de 365.25 a aussi des problèmes).

    Je sais que le script suivant calcule une date précise de la différence (mais peut-être pas la plus rapide):

            declare @d1 datetime ,@d2 datetime
        --set your dates eg: 
        select @d1 = '1901-03-02'
        select @d2 = '2016-03-01'

        select DATEDIFF(yy, @d1, @d2) -
            CASE WHEN MONTH(@d2) < MONTH(@d1) THEN 1
                 WHEN MONTH(@d2) > MONTH(@d1) THEN 0
                 WHEN DAY(@d2) < DAY(@d1) THEN 1
                 ELSE 0 END

         -- = 114 years

    Pour la comparaison:

             select datediff(day,@d1 ,@d2) / 365.2425
         -- = 115 years => wrong!

    Vous pourriez être en mesure de calculer des petites plages de division, mais pourquoi prendre une chance??

    Le script suivant peut aider à tester yeardiff fonctions (il suffit de l'échanger cast(datediff(jour,@d1,@d2) /365.2425 comme int) à ce que la fonction est): 

       declare @d1 datetime set @d1 = '1900-01-01'

   while(@d1 < '2016-01-01')
   begin
    declare @d2 datetime set @d2 = '2016-04-01'

    while(@d2 >= '1900-01-01')
    begin
        if (@d1 <= @d2 and dateadd(YEAR,     cast(datediff(day,@d1,@d2) / 365.2425 as int)      , @d1) > @d2)
        begin
            select 'not a year!!', @d1, @d2, cast(datediff(day,@d1,@d2) / 365.2425 as int)
        end

        set @d2 = dateadd(day,-1,@d2)
    end

    set @d1 = dateadd(day,1,@d1)
  end

    OriginalL'auteur Dmitri Rechetilov

  4. 0

    J'ai trouvé une meilleure solution. De ce fait l'hypothèse que la première date est inférieure ou égale à la deuxième date.

    declare @dateTable table (date1 datetime, date2 datetime)
insert into @dateTable 
select '2017-12-31', '2018-01-02' union
select '2017-01-03', '2018-01-02' union 
select '2017-01-02', '2018-01-02' union
select '2017-01-01', '2018-01-02' union
select '2016-12-01', '2018-01-02' union
select '2016-01-03', '2018-01-02' union
select '2016-01-02', '2018-01-02' union
select '2016-01-01', '2018-01-02' 
select date1, date2, 
case when ((DATEPART(year, date1) < DATEPART(year, date2)) and 
((DATEPART(month, date1) <= DATEPART(month, date2)) and 
(DATEPART(day, date1) <= DATEPART(day, date2)) ))
then DATEDIFF(year, date1, date2)
when (DATEPART(year, date1) < DATEPART(year, date2))
then DATEDIFF(year, date1, date2) - 1
when (DATEPART(year, date1) = DATEPART(year, date2))
then 0
end [YearsOfService]
from @dateTable
date1                   date2                   YearsOfService
----------------------- ----------------------- --------------
2016-01-01 00:00:00.000 2018-01-02 00:00:00.000 2
2016-01-02 00:00:00.000 2018-01-02 00:00:00.000 2
2016-01-03 00:00:00.000 2018-01-02 00:00:00.000 1
2016-12-01 00:00:00.000 2018-01-02 00:00:00.000 1
2017-01-01 00:00:00.000 2018-01-02 00:00:00.000 1
2017-01-02 00:00:00.000 2018-01-02 00:00:00.000 1
2017-01-03 00:00:00.000 2018-01-02 00:00:00.000 0
2017-12-31 00:00:00.000 2018-01-02 00:00:00.000 0
    Bonjour, comment est-il mieux que accepté de répondre?

    OriginalL'auteur K'Dubb