Forum Discussion

ashrafezz817's avatar
ashrafezz817
Regular Visitor
1 year ago
Solved

Converting to Time Error

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.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.