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

Answer 1

Try something like:

 SELECT id, NewsHeadline as news_headline, NewsText as news_text, state CreatedDate as created_on  FROM News   WHERE CreatedDate >= DATEADD(day,-7, GETDATE()) 

Answer 2

select id,     NewsHeadline as news_headline,     NewsText as news_text,     state,     CreatedDate as created_on     from News     WHERE CreatedDate>=DATEADD(DAY,-7,GETDATE()) 

Answer 3

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 >= 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)