How many days are left for a date [duplicate]

5

Does anyone have a query in sql server that returns how many days are missing for a date? I have the employee's date of admission, I need to know, how many days to complete 45 days, and how many days to complete 90 days.

    
asked by anonymous 21.07.2017 / 19:53

3 answers

6

Use DATEDIFF and match your date with GETDATE () that takes the current date, after that use some logic to send your email.

declare @DtInclusao Datetime = '16/07/2017'    

-- Dias transcorridos
SELECT DATEDIFF (DAY, @DtInclusao , GETDATE())

-- Dias que faltam  para 45 dias
SELECT 45 - DATEDIFF (DAY, @DtInclusao , GETDATE())

-- Dias que faltam  para 90 dias
SELECT 90 - DATEDIFF (DAY, @DtInclusao , GETDATE())


if DATEDIFF ( DAY , @DtInclusao , GETDATE()) = 5
  print 'enviar email'

As mentioned in the comments;

declare @funcionarios table
(   
    Nome varchar(100),
    DtInclusao Datetime 
)   

insert into @funcionarios values ('João', '01/06/2017') 
insert into @funcionarios values ('Maria', '05/06/2017') 
insert into @funcionarios values ('Paulo', '21/06/2017') 
insert into @funcionarios values ('Andre', '01/07/2017') 
insert into @funcionarios values ('Carlos', '11/07/2017') 

-- Dias transcorridos
SELECT nome, DATEDIFF (DAY, DtInclusao , GETDATE()) as DiasTranscorridos ,
(45 - DATEDIFF (DAY, DtInclusao , GETDATE())) as DiasP45,
(90 - DATEDIFF (DAY, DtInclusao , GETDATE())) as  DiasP90
from @funcionarios
    
21.07.2017 / 20:19
6

If you are using SQL Server, use DATEDIFF

Examples:

Difference of day: SELECT DATEDIFF ( DAY , '21/07/2017' , '30/07/2017' )

difference from month: SELECT DATEDIFF ( MONTH , '21/07/2017' , '21/09/2017' )

difference of year: SELECT DATEDIFF ( YEAR , '21/07/2015' , '21/07/2018' )

For your needs:

Declare @DataAdminissao datetime
     set @DataAdminissao = '20/06/2017'

SELECT DATEDIFF ( DAY , getdate() , @DataAdminissao + 45 )

The return of this example will be 14, when you arrive on the 5 or 3 day, you can put a proc to trigger an alert email.

    
21.07.2017 / 20:04
5

Example with Oracle:

select to_date('2017-07-21','YYYY-MM-DD') - to_date('2017-07-20','YYYY-MM-DD')
from dual;
    
21.07.2017 / 19:58