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.
