KB-28190: Filter in SQL Query using time value.

Question:

How can I query a certain period (initial and final date), while also adding filter it by hour within this period? (For example: the query must return data only from 5 P.M. to 8 P.M. every day in the same period).

Solution:

SQL Server features a function called DatePart(), which is responsible for extracting any piece of time (hour, day, month, etc) from a period. On query’s Where clause you can insert a script similar to this:

WHERE (E3Timestamp >=
#<%InitialDate%># and E3Timestamp < #<%FinalDate%>#) and
(DatePart(hh,E3TimeStamp) >= 17 and DatePart(hh,E3TimeStamp) < 20)

Where:

  • InitialDate is the variable for setting the query’s initial date/time; and 
  • FinalDate is the variable for setting the query’s final date/time. 

This means it will filter by initial and final date, and only where the time is higher than 5 but lower than 8 o’clock.

If this Query is used in E3Chart, these values will all be connected, i.e., a single line will link the last record of a day with the first record of the following day. No space will be left between then; this is the normal, expected behavior of E3Chart.

NOTE: If DatePart() function does not work in other databases, look for a similar function, or check for divergent syntaxes.

Print Friendly, PDF & Email

Este artigo foi útil? Was this helpful?

Classificação média - Average rating 0 / 5. Count: 0

Deixe seu Comentário

Seu endereço de e-mail não será publicado. Campos marcados com asterisco são obrigatórios *