Forum Discussion

andyor's avatar
andyor
Resolver I
3 years ago

Direct Query with Date parameters

I am using DQ mode to query a basic table on TransactionDate. The issue is that the database times are UTC time, but the server is in local time. For this purpose, a function ufnToUTCTime() is called for all queries. 

 

If I envoke the function directly on the WHERE clause on TranscationDate, the query will take forever to run, as it's converting every single row toUTCTime. So in T-SQL I would DECLARE and SETthe @startdate and @enddate at the start and use these parameters in my where clause i.e., WHERE TransactionDate BETWEEN @startdate and @endate. 

 

When I try and declare and set in Direct Query mode, I get the following error 

 

 

How can I restucture my statement, such that it will be accepted for Direct Query mode? I have a temportary workaround in the meantime to use DATEADD(HOUR,-10) for the TransactionDate start and end dates. This will require me to change all queries back and forth to HOUR,-11 HOUR, -9 etc when daylight savings kicks in through the year

Need Help

4 Replies

    • andyor's avatar
      andyor
      Resolver I
      3 years ago

      Thanks but don't think this will work as I need to use a database function ufnToUtcTime(), which converts the local time (server) to UTC time (database)

      • andyor's avatar
        andyor
        Resolver I
        3 years ago

        unless power BI has some inbuilt function that can do the same? (convert to UTC time)

  • FarmerKenny's avatar
    FarmerKenny
    Helper II
    3 years ago

    Can someone please solve this? I have the same issue. I want to keep the Date column that I am using as an expression in my Direct Query, AND also keep my parameter as a DATE data type, but Power BI is choking on that. It tries to evaluate it, but the query editor never finishes the query.