How to get last 7 days data from current datetime to last 7 days in sql server

Question

Hi I am loading table A data from sql server to mysql using pentaho when loading data i need to get only last 7 days data from sql server A table to mysql In sql server createddate column data type is like datetime AND In mysql created on column datatype is timestamp  Here I used below query but i am getting only 5 days data Please help me in this issue  select id      NewsHeadline as news headline      NewsText as news text      state      CreatedDate as created on       from News     WHERE CreatedDate BETWEEN GETDATE  -7 AND GETDATE   order by createddate DESC

User · Answer

you can use DATEADD function in your where clause like  select        where Createdate  gt   DATEADD day -7 GETDATE

User · Answer

I don t think you have data for every single day for the past seven days  Days for which no data exist  will obviously not show up   Try this and validate that you have data for EACH day for the past 7 days  SELECT DISTINCT CreatedDate FROM News  WHERE CreatedDate  gt   DATEADD day -7  GETDATE    ORDER BY CreatedDate   EDIT - Copied from your comment      i have dec 19th -1 row data 18th -2 rows 17th -3 rows 16th -3   rows 15th -3 rows 12th -2 rows  11th -4 rows 9th -1 row 8th -1 row   You don t have data for all days  That is your problem and not the query  If you execute the query today - 22nd - you will only get data for 19th  18th 17th 16th and 15th  You have no data for 20th  21st and 22nd   EDIT - To get data for the last 7 days  where data is available you can try  select id      NewsHeadline as news headline      NewsText as news text      state      CreatedDate as created on       from News     WHERE CreatedDate IN  SELECT DISTINCT TOP 7 CreatedDate from News order by createddate DESC

User · Answer

Try something like    SELECT id  NewsHeadline as news headline  NewsText as news text  state CreatedDate as created on  FROM News   WHERE CreatedDate  gt   DATEADD day -7  GETDATE

User · Answer

This worked for me    SELECT   FROM  users  where  created at  BETWEEN CURDATE  -7 AND CURDATE

User · Answer

select id      NewsHeadline as news headline      NewsText as news text      state      CreatedDate as created on     from News     WHERE CreatedDate gt  DATEADD DAY -7 GETDATE

User · Answer

To pull data for the last 3 days  not the current date     date timestamp   gt   curdate   - 3 AND date timestamp   lt  curdate     Example   SELECT    FROM user login WHERE age  gt  18 AND date timestamp   gt   curdate   - 3 AND date timestamp   lt  curdate    LIMIT 10

User · Answer

Hope this will help   select id      NewsHeadline as news headline      NewsText as news text      state      CreatedDate as created on       from News     WHERE CreatedDate  gt   cast dateadd day  -7  GETDATE    as date  and CreatedDate  lt  cast GETDATE   1 as date  order by CreatedDate desc

User · Answer

If you want to do it using Pentaho DI  you can use  Modified JavaScript  Step and write the below function   dateAdd d1   d   -7       d1 is the current date and  d  is the date identifier   Check the image below   Assuming current date is   22 December 2014     Hope it helps

User · Answer

DATEADD and GETDATE functions might not work in MySQL database  so if you are working with MySQL database  then the following command may help you   select id  NewsHeadline as news headline      NewsText as news text      state  CreatedDate as created on     from News     WHERE CreatedDate gt   DATE ADD CURDATE    INTERVAL -3 DAY     I hope it will help you

[sql] How to get last 7 days data from current datetime to last 7 days in sql server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008-r2

Examples related to pentaho

Examples related to pentaho-cde