Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MrPatrick
Helper I
Helper I

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.

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@MrPatrick 

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

 

 

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

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_DATE

 Best 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.

jdbuchanan71
Super User
Super User

@MrPatrick 

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;

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors