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 10 Answers
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()) select id, NewsHeadline as news_headline, NewsText as news_text, state, CreatedDate as created_on from News WHERE CreatedDate>=DATEADD(DAY,-7,GETDATE()) 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) 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
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 This worked for me!!
SELECT * FROM `users` where `created_at` BETWEEN CURDATE()-7 AND CURDATE() you can use DATEADD function in your where clause like
select ...... where Createdate >= DATEADD(day,-7,GETDATE()) 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.
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 :)
0Hope 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 ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoaXBtaW6CdoOOoaawZaSkeqix02ajmqukYoRusMCyqmackamubrLRqKRmm6Wnv6a602abmqyVqbausYytpmakkajBboOMnZiyq12eu26%2F0KVkrJ2iq7Kz