Home Time in SQL (Years, months and days)

Asked

Viewed 1,917 times

5

I need to create a SQL function that returns the employee’s home time, example: years old, months and days

So far I’ve managed to bring the years old and months, but I couldn’t get the logic of day yet, someone can help me ?

DECLARE @DTINI DATE, @DTFIM DATE

SET @DTINI = '2015-12-17'
SET @DTFIM = '2016-03-10'

SELECT  CAST(DATEDIFF(DAY,@DTINI,@DTFIM)/365.25 AS INT)     ANOS,
        CASE WHEN DATEDIFF(DAY,@DTINI,@DTFIM)/30  > 11 THEN 0
         ELSE DATEDIFF(DAY,@DTINI,@DTFIM)/30        END MESES
sql sql-server tsql

4 answers

2

Make sure it’s logical:

DECLARE @DTINI DATE, @DTFIM DATE

SET @DTINI = '2015-10-17'
SET @DTFIM = '2016-12-17'

SELECT 
    CAST(DATEDIFF(DAY, @DTINI, @DTFIM) / 365.25 AS INT) AS ANOS,
    DATEDIFF(d, @DTINI, @DTFIM) - 365 * CAST(DATEDIFF(DAY, @DTINI, @DTFIM) / 365.25 AS INT) AS DIAS,
    CASE 
        WHEN DATEDIFF(DAY, @DTINI, @DTFIM) / 30 > 11 THEN 0
        ELSE DATEDIFF(DAY, @DTINI, @DTFIM) / 30
    END AS MESES

I multiply the number of days of a year by the amount of YEARS so if it is 0 years it does not decrease the days of DAYS if it is 1 year it decreases the 365 so that it stays 1 year and 20 days etc...

1

Paul, this function may have problems with leap years, try to do the following:

DECLARE @DTINI DATE, @DTFIM DATE, @ANO INT, @MES INT, @DIA INT

SET @DTINI = '2015-01-01'
SET @DTFIM = '2016-12-31'

SET @ANO = DATEDIFF(YEAR, @DTINI, @DTFIM)
SET @DTINI = DATEADD(YEAR, @ANO, @DTINI)
SET @MES = DATEDIFF(MONTH, @DTINI, @DTFIM)
SET @DTINI = DATEADD(MONTH, @MES, @DTINI)
SET @DIA = DATEDIFF(DAY, @DTINI, @DTFIM)

SELECT @ANO AS ANO, @MES AS MES, @DIA AS DIA

0

SQL Creation of the Table

CREATE TABLE [tablexemplo](
    [DataAdmissao] [date] NULL
) ON [PRIMARY];
INSERT INTO tablexemplo values('2010-01-30');
INSERT INTO tablexemplo values('2010-02-28');
INSERT INTO tablexemplo values('2010-03-30');
INSERT INTO tablexemplo values('2010-06-29');
INSERT INTO tablexemplo values(GetDate());

SQL

  SELECT 
    DataAdmissao,
    CASE WHEN Mes < 0 THEN
        (Ano - 1)   
    ELSE
        Ano
    END as Ano,
    CASE WHEN Mes < 0 THEN
        (Mes + 12)  
    ELSE
        Mes
    END as Mes,
    Dia
FROM (
    SELECT 
        DataAdmissao, 
        Ano, 
        CASE WHEN Dia < 0 THEN
            (Dia + 30)  
        ELSE
            Dia
        END as Dia,
        CASE WHEN Dia < 0 THEN
            (Mes - 1)   
        ELSE
            Mes
        END as Mes
    FROM (
        SELECT DataAdmissao, 
            (YEAR(GETDATE()) - YEAR(DataAdmissao)) as Ano,
            (MONTH(GETDATE()) - MONTH(DataAdmissao)) as Mes,
            (DAY(GETDATE()) - DAY(DataAdmissao)) as Dia
        FROM tablexemplo 
        ) AS T
    )T1

Sqlfiddle: http://sqlfiddle.com/#! 3/0e783/1

Source

0

I did according to the example of durtto and it was like this:

DECLARE @DTINI DATE, @DTFIM DATE
DECLARE @DIA INT, @MES INT, @ANO INT


SET @DTINI = '2014-10-31'
SET @DTFIM = '2018-11-30'

SET  @ANO = (YEAR(@DTFIM) - YEAR(@DTINI)) 
SET  @MES = (MONTH(@DTFIM) - MONTH(@DTINI)) 
SET  @DIA = (DAY(@DTFIM) - DAY(@DTINI)) 

IF @DIA < 0
    BEGIN
        SET @DIA = @DIA + 30
        SET @MES = @MES - 1
        IF @MES < 0
                BEGIN
                    SET @ANO = @ANO -1
                    SET @MES = @MES + 12
                END  

    END
ELSE IF @MES < 0
                BEGIN
                    SET @ANO = @ANO -1
                    SET @MES = @MES + 12
                END             

PRINT @ANO 
PRINT @MES 
PRINT @DIA

Browser other questions tagged sql sql-server tsql

You are not signed in. Login or sign up in order to post.