Comparing date and time in different fields

Asked

Viewed 282 times

0

I have a table that contains the following fields type name (nvarchar), Date of type (Date) and time like (Time).

I need to do the comparison by period example: 08-02-2019 e 18:30:10 até 30-05-2019 e 07:45:11

How do I do this in sql server query

I was trying that way but I know it doesn’t work:

SELECT        pessoa.nome, acesso.data, acesso.hora
FROM            acesso 
INNER JOIN pessoa ON acesso.id_pessoa = pessoa.id
WHERE        (acesso.data >= @data1) AND (acesso.data <= @data2) and   (acesso.hora >= @horaInicio and acesso.hora <= @horafim)
sql sql-server

  • Combine your date and time fields in a datetime field and make a single check if you are in range.

  • I cannot change the structure of the bank, because it is third party and there are already several data.

  • I did not speak of changing the bank structure but rather combining the fields in your select to make the comparison. I use neither Microsoft SQL Server nor Sqlite to illustrate how to do it.

2 answers

2

In the Sqlserver 2008 R2 a possibility would be the following:

SELECT
  pessoa.nome,
  acesso.data,
  acesso.hora     
FROM acesso
INNER JOIN pessoa ON acesso.id_pessoa = pessoa.id
WHERE CAST(acesso_data AS datetime) + CAST(acesso_hora AS time) BETWEEN 
CAST(@data1 AS datetime) + CAST(@horaInicio AS time) AND CAST(@data2 AS datetime) + CAST(@horaFim AS time)

  • But the question is about sqlite and not SQL Server

  • @Sorack, blza!! So I think the mba made a mistake when he entered the tags in the body of the question, he describes: How do I do this in sql server query

  • 1

    This also works @Oliveira, but as I am working with several versions of sql server and the CAST not available in 2005 and 2008, I opted for that form. Congratulations and Thank you.

  • @blza, you may contribute other Dev’s with this version of Sqlserver.

  • I edited the question by removing the sqlite tag to not confuse but anyone. Thank you.

  • @Oliveira will definitely contribute yes. Hugs

Show 1 more comment

0


I solved the problem as follows:

SELECT P.nome, A.data, A.hora
  from acesso as A
       inner join Pessoa as P on A.id_pessoa = P.id
  where A.data between @data1 and @data2
        and 1 = (case when A.data = @data1 
                           then case when A.hora >= @horaInicio then 1 else 0 end
                      else 1 end)
        and 1 = (case when A.data = @data2 
                           then case when A.hora <= @horafim then 1 else 0 end
                      else 1 end);

Browser other questions tagged sql sql-server

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