Falakienos Falakienos - 10 months ago 159
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()-7
to get the period but I have no idea how to filter the time.

Thanks in advance for the help :)

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