cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Benjiman89 Frequent Visitor
Frequent Visitor

SYSDATE from Oracle DB different in PowerBI

Hi,

 

The following SQL gives different answers when imported into PowerBI compared to running in a local Oracle server.  Obvisouly I am pointing the PowerBI data source at the same local database - but why is it different?

 

Power BI treats the week as Mon-Sun but my local server treats it as Sun-Sat. 

 

SELECT TO_NUMBER(TO_CHAR(SYSDATE,'D')) FROM DUAL

Please could someone explain why?

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: SYSDATE from Oracle DB different in PowerBI

Hi @Benjiman89,

 

Based on my research, in Oracle, you could use the NLS_TERRITORY parameter to set which day the week should start on. You can change it just for your session.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Microsoft v-yulgu-msft
Microsoft

Re: SYSDATE from Oracle DB different in PowerBI

Hi @Benjiman89,

 

In my test, this query returns the same answer in Oracle and Power BI desktop.

 

Result in Oracle                                                                                 Result in Power BI desktop

Result in Oracle.PNGResult in Power BI.PNG

 

Are you sure the Power BI desktop is hosted in the same server where installed Oracle server? What is your desktop version? 

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Benjiman89 Frequent Visitor
Frequent Visitor

Re: SYSDATE from Oracle DB different in PowerBI

Thanks fo the reply @v-yulgu-msft

 

When I run this in my Oracle server I get the following (for Thursday today):

Capture11.PNG

 

 

 

 

Could it be a setting on my Oracle server end defaulting to Monday as start date for week?

 

Thanks,

Ben

Microsoft v-yulgu-msft
Microsoft

Re: SYSDATE from Oracle DB different in PowerBI

Hi @Benjiman89,

 

Based on my research, in Oracle, you could use the NLS_TERRITORY parameter to set which day the week should start on. You can change it just for your session.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)