Converting to Time Error

[ashrafezz817]

Hello,

I am currently working in DirectQuery mode with Oracle and encountering an issue while trying to convert a number data type column to time. Initially, when attempting to convert the column directly to a time format, I received the following error:

ORA-01847: day of the month must be between 1 and the last day of the month.

After converting the column to text and trying to extract the hour using the formula:

DAX

Hour = HOUR(TIMEVALUE([inv_time]))

I encountered a new error stating that the value was not a valid month.

This issue does not occur when I switch to Import Mode, but I need to work in DirectQuery mode.

Could you please advise on how to resolve this issue? 

Thank you.

[Anonymous · Accepted solution]

Hi @ashrafezz817 

I put your question to the test:

First, determine if the data in your database is in time format.

Connect using DirectQuery mode in desktop. And check that the data type is correct.

Create a column or measure.

Column Hour = HOUR('timeTable'[INV_TIME])

Measure Hour = HOUR(SELECTEDVALUE('timeTable'[INV_TIME]))

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.