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

[rodneyc8063]

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.

[v-yetao1-msft · Accepted solution]

Hi @rodneyc8063 

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.

[rodneyc8063]

Thanks for the detailed explanation @v-yetao1-msft !!

[amitchandak]

@rodneyc8063 , Try like

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

[rodneyc8063]

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 😛