How many days to a date

Asked

Viewed 1,988 times

5

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

sql sql-server query

  • How many days are left to complete 45 days ??? a little confused this , could specify better your question and and which bank you are using.

  • @Marconciliosouza I have the date of admission of the employee, from there I want to know how many days to complete 45 working days, and then how many days are left to complete 90 days and when it is 5 days to 45 I will shoot email notifying and consequently I will do it when I am 5 days to complete 90 days, understood better now? Here I use SQL Server

  • managed to solve his problem ?

  • I managed, I used your example even, to put 45 - Datediff, it worked, thanks, how do I put the post as solved?

  • only you mark how you accept the answer .

3 answers

6

If you are using SQL Server, use DATEDIFF

Examples:

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

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

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

For your need :

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 5 or 3 day, you can put a proc to trigger an alert email.

  • Ricardo, I understood your answer, but I’m wondering how many days are left to arrive on a date, and not the difference of days between them.

  • Right, in the first data field use GETDATE(), which is the current date of the system, and in the last one put the date you want... yes you will have the return you need. Take the test.

  • I have the employee’s admission date, and I want to know how many days until he completes 45 working days, and then how many days are left to complete 90 days and when it is 5 days to 45 I will shoot email notifying and consequently I will do it when I am 5 days to complete 90 days, understood better now? Here I use SQL Server

  • @Marcilioeloi I just edited my answer and put another example. I believe that already suits you.

  • I get it, but you set a date, I have several employees with different admission date.

6


Use the DATEDIFF and match your date with the GETDATE() which 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

  • I understood what you did, but I have about 10 employees and they have different admission dates, as I will return the remaining days to complete the 45 and 90 days?

  • In place of @Dtinclusao use the field of your table

5

Example with Oracle:

select to_date('2017-07-21','YYYY-MM-DD') - to_date('2017-07-20','YYYY-MM-DD')
from dual;

Browser other questions tagged sql sql-server query

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