Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

SET DATEFIRST 1 in Direct Query mode

I am completely out of ideas. 

 

My data is all structured to look at Monday as the first day of the week but I am using an Azure SQL backend so I can't change the default value which is set to Sunday. The outcome is that all of my data is a week out by the time it gets to power BI because the system user I created uses the default value.

 

I have tried:

SET Datefirst - "Incorrect syntax near the keyword 'SET'"

EXEC sp_...... "Incorrect syntax near the keyword 'EXEC'"

OpenQuery - Not supported

Change the default value of datefirst on the server level - not supported

Change the default value of datefirst on the db level - not supported

Change the default value of datefirst on the user level - not supported

 

My unerstanding is that Power BI wraps up my quesry as follows SELECT * FROM ([My Query]). I'd be fired if I tried anything that crude, its the kind of thing you do when you're starting out, is this really the best they have? 

 

Is Power BI definitely ready yet? It seems to be missing SO MANY basic features you'd expect with a reporting suite. 

 

I'm not going to buy a full SQL server just to alter the datefirst variable and I think its silly to suggest I'd have to. 

 

Can anyone help out or point me in the right direction?

 

Thanks

Need Help

7 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

    Monday is the first day of week in Azure SQL, you want to change date format Sunday as the first day of this week when you get date in Power BI desktop in direct Query Mode, right? If it is, we can not change it, you connect date of database directly rather than importing data to the local when you use Direct Query model, please review this feature request below and vote it.

    Improve Direct Query Date Time Handling

    Thanks,
    Angelia

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-huizhn-msft,

       

      Thanks for the reply. 

      I can;t get Azure to see Monday as the first day no matter where I go. I think a few too many features have been turned off unfotunately .

       

  • miltenburger's avatar
    miltenburger
    Helper V
    8 years ago

    Hi Anonymous,

     

    I was sturggling with this problem also to set datefirst 1 in my DirectQuery.

    What i had to do is get the data from last week (monday till sunday), what i created was following:

     

    SELECT *
FROM mytable
WHERE CreatedDateTime >= DATEADD(day, -(CASE WHEN DATEPART(DW, GETDATE()) = 1 THEN 7 ELSE DATEPART(DW, GETDATE()) - 1 END + 6), CONVERT(DATE, GETDATE())) 
    AND CreatedDateTime <  DATEADD(day, 1 - CASE WHEN DATEPART(DW, GETDATE()) = 1 THEN 7 ELSE DATEPART(DW, GETDATE()) - 1 END, CONVERT(DATE, GETDATE()))
    AND Minutes is not null
    AND Minutes > 0
    AND TicketNumber like 'CH%'

    Hope it helps you to get to set the datefirst to 1, it's all based on my where clause.

    Kind regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      miltenburger thanks for the reply,

       

      It feels to me like they have severly regressed the softwware, I can't understand why such a tiny and simple change should be made so complicated

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous,

        You can create an idea here, Power BI will become better after your support.

        Best Regards,
        Angelia