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

Asked

Viewed 1,574 times

0

I have a problem need to filter a query under the following conditions : I have the Data field and I have the time field both in varchar , when I try to make 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 have any possibility to manipulate these fields into varchar ? If these date and time fields were of type datetime surely would be much simpler query.

sql sql-server

  • Already tried to convert the data?

  • the date and time field are different columns, they are not in datetime format

  • Is the data always the same size? Ex. Date has 8 digits and Time 5?

  • BETWEEN '10:00' and '10:00' - BETWEEN amid equal hours?

  • @Jeangustavoprates yes. same size!

  • @ramaral between with the two equal values is the same thing to match the desired field. would have a way to make another filter???

  • I only asked because it is not usual to make a BETWEEN between something that has nothing between(BETWEEN). That could be the reason for the query do not return anything. The normal way of doing is and Hora = '10:00'

  • It would be easier to help if you put, in the question, an example with half a dozen existing records in the table.

  • @Hansmiller, you can see physically in the database if this query actually returns records?

  • If you post the table structure(column data type) and some records you can see if the query is correct. The fact that she returns null, in itself, does not indicate that it is bad, there may be no records that satisfy it.

  • @Hansmiller can answer ?

Show 6 more comments

2 answers

2

You should not treat separate date and time, you should concatenate them by filtering through them. In your example, the DBMS will try to bring the records between the dates "04/17/2015" and "04/18/2015", 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 "17/04/2015 10:00" and "18/04/2015 10:00".

Try it like 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

0

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

But using what we have today, I would go on the @Silvair line, but since I’m not a fan of coding to make queries, follow 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'

Browser other questions tagged sql sql-server

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