HELP! How to perform Datediff with difference between columns and not current_date?

Asked

Viewed 122 times

0

I’m in need of a simple, data difference (DIA) feedback on Firebird, but I’m not getting the feedback and try many ways ...

the Command below works ...

Datediff (day from Max (distinct TRAVEL.DATE) to current date) as Dias_viag,

However, I don’t want it to make a difference about the current data but another column ... An example that doesn’t work, but needs the difference ..

Datediff (Max Day (distinguished TRAVEL.DATE) for TRAVEL.DATE arrives) as Dias_viag,

Can you help me?

Datediff (Max Day (distinguished TRAVEL.DATE) for TRAVEL.DATE arrives) as Dias_viag,

I hope you return example:

travel.date left = 20/09/2019 - travel.date arrives = 21/09/2019 {1}

1 answer

0

Friend, you can use the command Datediff() informing the columns you want.
For example imagine you have the following table:

create table Dados(
    id int not null primary key,
    data_ini date default current_date,
    data_fim date default current_date
);

In case you want to catch the days of difference enter data_ini and data_fimcan use select:

select datediff(day, data_ini, data_fim) from dados;

Note, the command 'DATEDIFF()' accepts entries in date,time and timestamp format, check if the type of data recorded in the columns you want to calculate is the same type as the function accepts, otherwise you should format them.

  • Hello Friend, yes the format of the entries are of type date.. I continue with error ... Select DRIVERS.ID as ID, DRIVERS.CODE as CODE, DRIVERS.NAME as NAME, DRIVERS.CEL as CEL, Max(Distinct PORTARIA_SAIDA.CHEGADA_DATA_HORA) As DT_ULT_CHEG_PORT, Count(Distinct VIAGENS.ID) As QDT_VIAGENS, Datediff(DAY, VIAGENS.DATACHEGA , VIAGENS.DATASAIDA) The DIAS_VIAG,

  • DateDiff(Hour From Max(Distinct PORTARIA_SAIDA.CHEGADA_DATA_HORA) To Current_Timestamp ) As HR_DESC,
 Max(Distinct VIAGENS.CODIGO) As ULTIMA_VIAGEM
From
 MOTORISTAS Left Join
 PORTARIA_SAIDA
 On MOTORISTAS.ID = PORTARIA_SAIDA.ID_MOTORISTA Left Join
 TRIPS On TRIPS.ID_MOTORISTA = DRIVERS.ID Where DRIVERS.COMPANY = 1 Group By DRIVERS.ID, DRIVERS.CODE, DRIVERS.NAME, DRIVERS.CEL, TRIPS.COMPANY Having Max(Case When PORTARIA_SAIDA.CHEGADA_DATA_HORA Is Null Then 1 Else 0 End) = 0

  • This little guy I can’t make work. Datediff(DAY, TRAVEL.DATAARRIVES , TRAVEL.DATAOUTPUT) The DIAS_VIAG, if I put it like this it works.... Datediff(DAY, from VIAGENS.DATACHEGA) To Current_date) As DIAS_VIAG, But I do not want to compare with current date and yes with date..

  • @Victorandrade Have you noticed that you are passing the arrival date before the departure date? The correct would be the opposite, first the shorter date after the longer date. Other than that you are giving a group by in 'TRAVEL.COMPANY' and this field is not being returned in the query, will give error, or remove this group by or add it in the query.

  • @That’s what the return to command looks like select MOTORISTAS.ID as ID, 
 MOTORISTAS.CODIGO as CODIGO, 
 MOTORISTAS.NOME as NOME,
 VIAGENS.DATASAIDA SAIDA,
 VIAGENS.DATACHEGA CHEGADA
From MOTORISTAS
Left Join VIAGENS On VIAGENS.ID_MOTORISTA = MOTORISTAS.ID 
Where MOTORISTAS.EMPRESA = 1
Group By MOTORISTAS.ID, 
 MOTORISTAS.CODIGO,
 MOTORISTAS.NOME, 
 MOTORISTAS.CEL

  • I fixed the date seq, smaller then larger and tbm removed Viagens.Empresa from Group. And I consulted again... nothing ... I made the command that suggested and returned the information indicated in select , just took the group. ... Strange is that the initial query to the original that I just weighed , the way I was just changing the field dates to current_date it runs right, I would need to find a way to work, What I’m not getting is to understand how I would put a second field to compare the dates ...qq thing could call you or Zap 77991791369

  • @Victorandrade, could you post the return of the select that I asked there in the previous comment? It is important to keep the group by clause because you use it in your select. With the return of this select we will be able to get an idea of the data that are going into the field trips.date and trips.date, I believe that the problem is not the command datadiff() but yes as you are riding your select.

Show 3 more comments

Browser other questions tagged

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