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

Grating

Are you sure you have 7 days data? – Obscurantism

yes i am sure i have 7 days data – Grating

There has to be data for every single day in the past 7 days – Obscurantism

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 – Grating

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

Quart

Another way to do it to get the most recent 7 days, including the current date: [rest of query]... WHERE CreatedDate BETWEEN (SELECT CAST(DATEADD(day,-6, GETDATE()) AS DATE)) AND (SELECT CAST(GETDATE() AS DATE)) – Teutonic

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

Messinger

Also make sure you have past 7 days data in the table News – Messinger

i have past data from 2010 to 2014 dec 19th – Grating

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)

Obscurantism

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 – Grating

In this situation by leaving 14th and 13th data if i want get 12th,11th,9th,8th data how can i do that help me – Grating

Thanks For above query it is giving last 7 records not giving last 7 days records – Grating

Answer 4

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>= DATE_ADD(CURDATE(), INTERVAL -3 DAY);

I hope it will help you

Father

Answer 5

To pull data for the last 3 days, not the current date :

date(timestamp) >= curdate() - 3
AND date(timestamp) < curdate()

Example:

SELECT *

FROM user_login
WHERE age > 18
AND date(timestamp) >= curdate() - 3
AND date(timestamp) < curdate()

LIMIT 10

Homologous

If someone asked have you cheated on your diet in the last 7 days, and you had this morning then it would count. I personally don't feel LAST X DAYS should exclude today. Use Past instead of Last. I can occasionally see merit in feeling the other way, for example: "I spent the last few years poor" (after winning the lottery today). – 3d

Answer 6

This worked for me!!

SELECT * FROM `users` where `created_at` BETWEEN CURDATE()-7 AND CURDATE()

Portecochere

Answer 7

you can use DATEADD function in your where clause like

select ...... where Createdate >= DATEADD(day,-7,GETDATE())

Sphingosine

Answer 8

CreatedDate > (select dateadd(WEEK, -1, getdate()))

you can search CreatedDate in future week too by reversing the sign of 1 (change -1 to 1):

CreatedDate > (select dateadd(WEEK, 1, getdate()))

In place of 'WEEK' you can use 'DAY', 'MONTH' or 'YEAR' as per your need. I hope this helps.

Rhettrhetta

Answer 9

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

Sejm

Answer 10

Hope this will help,

select id,    
NewsHeadline as news_headline,    
NewsText as news_text,    
state,    
CreatedDate as created_on      
from News    
WHERE CreatedDate >= cast(dateadd(day, -7, GETDATE()) as date)
and CreatedDate < cast(GETDATE()+1 as date) order by CreatedDate desc

Provenance