How to Get Last 7 Days Data from Current Datetime to Last 7 Days in SQL Server

SQL WHERE clause to pick data for last 7 days

use where clause like below

 select t.* from your_table t
 where CONVERT(datetime, creation_date,11)>= DATEADD(day,-7, GETDATE())

How to select last seven day's dates in SQL Server

If your expected results listed in the question are exactly what you are looking for, this will do the trick:

SELECT * FROM (
    SELECT CAST(GETDATE() AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -2, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -3, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -4, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -5, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -6, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -7, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
) AS the_table

results:

Last_Seven_Days_Dates
---------------------
2018-07-05
2018-07-06
2018-07-07
2018-07-08
2018-07-09
2018-07-10
2018-07-11
2018-07-12

Since you stated you'll want these in a temp table, I've updated the answer to make it a subquery, a little easier to join or do something with.

Additionally if you need it in a temporary table you could do this:

SELECT * INTO #TempTableName FROM (
    SELECT CAST(GETDATE() AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -2, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -3, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -4, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -5, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -6, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -7, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
)

How can I select the data from the last 7 days between hours

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

SQL Get last 7 days from event date

If you have values for all dates, without gaps, then you can use window functions with a rows frame:

select
    date,
    count(*) cnt
    sum(count(*)) over(order by date rows between 7 preceding and current row) cnt_d7
from mytable
group by date
order by date

Select Last N days data based on datetime column

In MariaDB 10.2.32 you can use DENSE_RANK() window function:

SELECT *
FROM (
  SELECT *, DENSE_RANK() OVER (ORDER BY DATE(timer) DESC) rnk
  FROM tablename
) t
WHERE rnk <= 3

See a simplified demo.

---

Related Topics