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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors