Date filter within date filter

[DoBBers]

Hi,

I have a basic knowledge of SQL, so apologies first of all.

I have a database, which stores a new set of row data every 5 minutes. I would like to create a report on this data which include two date/time filters and I am not sure on how to go about this.

For example, my report would show a range of data for the last month and for this I am using the following WHERE clause:

(DateTime between (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0)))

What I would also like to do is only show data that was stored between two timeframes each day i.e. between 9am and 6pm.

If anyway can assist in me getting this filter together, as I have been told it should be possible.

many thanks

 

[jmk418]

Hi
Using the convert function you can look at just the time piece of a datetime field. The following will return the current time only:

 convert(varchar(8), getdate(), 108)

to show just those specific hours during the time range specified just add to your where clause such as:

datetime between DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0) and (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))
and
convert(varchar(8), datetime, 108) between '09:00:00' and '14:00:00'

hope this helps