Forum Discussion

rodneyc8063_1's avatar
rodneyc8063_1
Icon for Helper V rankHelper V
5 years ago
Solved

DAX CONVERT - Error with converting to datetime? CONVERT(20200527, DATETIME)

I was playing around with the DAX function CONVERT and was specifically trying to change an integer value to a datetime value.

 

So for example I have an integer column with the value of 20200527

 

I thought this would be easy to just simply write CONVERT(20200527, DATETIME) and I should get my date. 

 

But apparently this gave an error?

 

I tried to search around and found someone had suggested the following formula

 

New Column = CONVERT(COMBINEVALUES("/",LEFT([DATE KEY],4),MID([DATE KEY],5,2),RIGHT([DATE KEY],2)),DATETIME)

 

Which works, but its just tedious to have to write out all the extra LEFT, MID, RIGHT etc.

 

Looking at this link: https://dax.guide/convert/

 

I see the following works just fine

 

EVALUATE
    {
        ( "Date1", CONVERT ( "12/25/1966", DATETIME ) ),
        ( "Date2", CONVERT ( "12-25-1966", DATETIME ) ),
        ( "Date3", CONVERT ( 12345,        DATETIME ) ),
        ( "Date4", CONVERT ( FALSE,        DATETIME ) )
    }
 
I can see CONVERT(12345, DATETIME) works (gives a weird result, but still works). 
 
I know there are other ways via Power Query and other methods to convert an integer column to a datetime. I just got excited that I found this new CONVERT function and thought this would make life super easy, but was just thrown off when I noticed I can not just throw a straight integer into it and get a datetime.
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi rodneyc8063_1 

    If you directly enter a numeric value in the position of the expression, then the returned result is based on 1899/12/30 0:00:00 plus the numeric value .

    For example :

    Because Power BI doesn't know how to divide the numeric value into year, month, day, hour, minute and second .So the way you said to plug a straight integer field in without having to parse it is not feasible .

    If you want to convert integer to date/time format , through Power Query or other dax .

     

    Best Regards

    Community Support Team _ Ailsa Tao

     

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    rodneyc8063_1 , Try like

    a new column =date(Quotient([Number], 10000) , Quotient(mod([Number], 10000),100), mod(mod([Number], 10000),100))

    • rodneyc8063_1's avatar
      rodneyc8063_1
      Icon for Helper V rankHelper V
      5 years ago

      Thanks amitchandak for the suggestion

       

      I believe this should work as well, I just wish with the CONVERT function, we can just plug a straight integer field in without having to parse it, or put in sub functions. Was just curious if there was another (even lazier) way to convert 😛 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi rodneyc8063_1 

    If you directly enter a numeric value in the position of the expression, then the returned result is based on 1899/12/30 0:00:00 plus the numeric value .

    For example :

    Because Power BI doesn't know how to divide the numeric value into year, month, day, hour, minute and second .So the way you said to plug a straight integer field in without having to parse it is not feasible .

    If you want to convert integer to date/time format , through Power Query or other dax .

     

    Best Regards

    Community Support Team _ Ailsa Tao

     

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