SQL Question
How can I select the data from the last 7 days between hours
I'm trying to query the data created 7 days ago but I need only the period of 11PM and 7AM. I'm actually I'm using SQL Server.
The table looks like that:
COD CREATED
---------------------------------------
6001 2017-11-22 22:03:23.5007015 -02:00
6002 2017-11-22 23:03:24.3593854 -02:00
6003 2017-11-23 06:03:24.7344270 -02:00
6004 2017-11-23 13:03:25.1680686 -02:00
6005 2017-11-23 23:56:00.6874197 -02:00
6006 2017-11-24 05:33:58.4243013 -02:00
6007 2017-11-24 22:40:08.3255570 -02:00
6008 2017-11-24 23:42:50.9885384 -02:00
And that's what I really want to query
COD CREATED
---------------------------------------
6002 2017-11-22 23:03:24.3593854 -02:00
6003 2017-11-23 06:03:24.7344270 -02:00
6005 2017-11-23 23:56:00.6874197 -02:00
6006 2017-11-24 05:33:58.4243013 -02:00
6008 2017-11-24 23:42:50.9885384 -02:00
I now I can use
between getdate() and getdate()-7Thanks in advance for the help :)
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
I can use between getdate() and getdate()-7 to get the period but I have no idea how to filter the time.
You can just use DATEPART(HOUR, datetimevalue), to get the hour.
For example, if you want only times from 11pm to 7pm:
SELECT *
FROM theTable
WHERE (DATEPART(HOUR, CREATED) > 22 --greater than 10pm hour
OR --OR
DATEPART(HOUR, CREATED) < 8 --less than 8pm hour
)
--put any other datediff logic here
Produces output:
COD CREATED
6002 2017-11-22 23:03:24.3594 -02:00
6003 2017-11-23 06:03:24.7344 -02:00
6005 2017-11-23 23:56:00.6874 -02:00
6006 2017-11-24 05:33:58.4243 -02:00
6008 2017-11-24 23:42:50.9885 -02:00
In this case I used datetimeoffset(4) for the CREATED column to allow for the -02:00 timezone.
If you want to see the full code: http://sqlfiddle.com/#!6/963a1/7/0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download