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.
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.
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
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.
Example with Oracle:
select to_date('2017-07-21','YYYY-MM-DD') - to_date('2017-07-20','YYYY-MM-DD')
from dual;