Can i use a common SQL Function in a Power BI importing query

[Yaotel]

Hi, i'm trying to import data from a MongoDB via ODBC Driver with this SQL Query:

SELECT column, NOW() FROM table WHERE orderDate >= DATE_SUB(NOW(),INTERVAL 5 DAY)

but the 'NOW()' function returns null. Apparently sql functions doesn't work in Power BI SQL queries. Is there any solution or other way to deal with that ? The query needs to happen at the same time as the import to limit the data.

[v-binbinyu-msft · Accepted solution]

Hi @Yaotel ,

Does a datetime field exist in your database? if so, try filtering using a field that actually exists in the database table.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Yaotel]

It really makes sense, I will try it. Thank you!

[Anonymous]

Hi,

you could try something like this:

declare @now datetime
declare @lastweek datetime

set @now = CAST(getdate() as date)
set @lastweek = CAST(DATEADD(day, -5, @now) as date)

SELECT [column] FROM [table]
where [date] > @lastweek

Regards,

Max

[Yaotel]

Thank you, but doesn't work

[Syk]

Try using 'GETDATE()' in place of NOW() and see if that helps

[Yaotel]

Thank you, but it doesn't work 😕