Forum Discussion
Restricting data import by date - MySQL
I've got a database where all the tables go back to ~2013 but I'm only interested in the last 12 months. Currently I either import the whole lot and then filter it (which is incredibly slow and prone to hitting timeout limits on the SQL server) or I use an explicit SQL query with 'where event_date > 2021/09/30' to create the query, but that query doesn't keep up as time goes on so I still have to filter for 12 months as a subsequent step.
I know I can use parameters to put that date in as '12 months' or whatever but I have spent most of a day looking at this and still hve no idea how I would do so. Can someone help me out?
My query currently looks like this :
let
Source = MySQL.Database("PBiDB", "DB", [ReturnSingleDatabase=true, Query="select * from agent_log_backup where event_time > '2021/01/01';"])
in
Source
And I have no idea how I would get a parameter to replace that date string.
Why not make the start date dynamic on your query? In T-SQL it would be.
WHERE Event_Date >= DATEADD(MONTH,-12,CONVERT(DATE,GETDATE()))
3 Replies
Why not make the start date dynamic on your query? In T-SQL it would be.
WHERE Event_Date >= DATEADD(MONTH,-12,CONVERT(DATE,GETDATE()))Great thank you, I cna't believe I hadn't thought of this beforehand.
for the record im in MySQL so the query was
select * from sales where order_date> now() - INTERVAL 12 month;
Hi, MrPatrick ;
Please try it
select * from agent_log_backup where event_time >= (current_date - INTERVAL '12 months')or
select * from agent_log_backup where event_time BETWEEN DATE_SUB( CURRENT_DATE, INTERVAL 12 MONTH ) AND CURRENT_DATEBest Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
