SQL Home Time (Years, months and days) [duplicate]

Question

SQL Home Time (Years, months and days) [duplicate]

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)
#4 by (0 votes)

5

I need to create a function in SQL that returns employee's time, for example: years , months and days

So far I've been able to bring the years and months , but I could not reach the day's logic yet, / p>

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

asked by anonymous 30.12.2015 / 12:58

4 answers

Capture image by url Themes, styles and support Library android

score 2 · Answer 1

Check if logic is true:

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 being so if it is 0 years does not decrease the days of the DAYS if it is 1 year decreases the 365 doing so that it stays 1 year and 20 days etc ...

score 1 · Answer 2

Paul, this function may have problems with leap years, try doing 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

score 0 · Answer 3

SQL

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: link

Source

score 0 · Answer 4

I followed the example of durtto and it looks 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