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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
wasonj1
Frequent Visitor

SQL Statement Variable Date Range

Hi There,

I have a query that is using an SQL statement that is currently pulling data between 2018-07-01 and 2019-07-01. I want to update the SQL statement to be able to pull data between the current date and 2 years older without hardcoding the dates. Can someone tell me how to update my SQL statement to do this?

powerbi_sql_datevariable.jpg

 

1 ACCEPTED SOLUTION
VasTg
Memorable Member
Memorable Member

@wasonj1 

 

The SQL will be little bit different with Oracle  or SQL Server.

 

Here are the examples. Replace your WHERE clause/line.

 

 

ORACLE:
WHERE M.Response_Date BETWEEN TRUNC(SYSDATE) AND TRUNC(ADD_MONTHS(SYSDATE,-24),'MONTH')

SQL SERVER:
WHERE M.Response_Date BETWEEN CAST(GETDATE() as DATE) AND DATEADD(MONTH,-24,CAST(GETDATE() as DATE))

 

 

If this helps, mark it as a solution

Kudos are nice too

Connect on LinkedIn

View solution in original post

4 REPLIES 4
VasTg
Memorable Member
Memorable Member

@wasonj1 

 

Why don't you just substract the date from sysdate?

Whats your Database?

 

Connect on LinkedIn
wasonj1
Frequent Visitor

Hi @VasTg,

I have very little experience with SQL, could you should me what that line of code would look like? I tried a variety of Date() and Sysdate() functions but always got an error message. I do not know what the database is, though I could ask someone to find out if its important (what types are there?).

Thanks for you help! 

VasTg
Memorable Member
Memorable Member

@wasonj1 

 

The SQL will be little bit different with Oracle  or SQL Server.

 

Here are the examples. Replace your WHERE clause/line.

 

 

ORACLE:
WHERE M.Response_Date BETWEEN TRUNC(SYSDATE) AND TRUNC(ADD_MONTHS(SYSDATE,-24),'MONTH')

SQL SERVER:
WHERE M.Response_Date BETWEEN CAST(GETDATE() as DATE) AND DATEADD(MONTH,-24,CAST(GETDATE() as DATE))

 

 

If this helps, mark it as a solution

Kudos are nice too

Connect on LinkedIn
wasonj1
Frequent Visitor

It was an SQL server, I had to swap the CAST and DATEADD calls but that worked perfectly. Thank you very much!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors