How to get date and time in different columns through a select Sql server?

Question

How to get date and time in different columns through a select Sql server?

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I'm having a problem filtering a query under the following conditions: I have the Date field and I have the field time both in varchar, when I try to do the query like this: ex:

     select Serial, Latitude,Longitude,Data,Hora from [CheckPoint]
     where Data BETWEEN '20150417' and '20150418'
     and Serial= '120904'
     and Hora BETWEEN '10:00' and '10:00' order by Hora

the query returns me null; would you have any possibility of manipulating those fields in varchar? If these date and time fields were of type datetime it would certainly make the query query much simpler.

sql sql-server

asked by anonymous 20.04.2015 / 14:58

2 answers

Pass control as parameter Show only positive delay days in SQL query?

score 2 · Answer 1

You should not treat date and time separately, you should concatenate them by filtering through them. In your example, the DBMS will try to bring the records between the "04/17/2015" and "04/18/2015" dates, ok, two days apart. But when you add, separately, the condition between the hours "10:00" and "10:00". As there is no time between 10:00 and 10:00, there is no record.

If you want to bring records between "04/17/2015 10:00 AM" and "04/18/2015 10:00 AM".

Try this:

DECLARE @DataInicial AS VARCHAR(10); DECLARE @DataFinal AS VARCHAR(10);
DECLARE @HoraInicial AS VARCHAR(5); DECLARE @HoraFinal AS VARCHAR(5);
SET @DataInicial = '20150417';SET @DataFinal = '20150418';
SET @HoraInicial = '10:00';SET @HoraFinal = '10:00';

SELECT
    Serial, Latitude,Longitude,Data,Hora
FROM [CheckPoint]
WHERE Data >= CAST(@DataInicial + ' ' + @HoraInicial AS DATETIME) 
AND   Data <= CAST(@DataFinal + ' ' + @HoraFinal AS DATETIME)
AND Serial= '120904'
ORDER BY Hora

score 0 · Answer 2

I recommend that you create a third column of type Date, where the default value is CAST(Data +' '+ Hora) . So you will have a column with the right type to make your filters.

But using what we have today, I would go in the line of @Silvair, but as I am not a fan of coding to make queries, here is my suggestion:

select T.* 
  from (select Serial, Latitude, Longitude, Data, Hora, CAST(Data +' '+ Hora) as DataHora 
          from [CheckPoint]
         where Serial= '120904') as T
 where T.DataHora BETWEEN '2015-04-17 10:00' and '2015-04-18 10:00'