cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rodneyc8063
Helper V
Helper V

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.
1 ACCEPTED SOLUTION
Ailsa-msft
Community Support
Community Support

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 :

Ailsa-msft_0-1622450538917.png

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.

View solution in original post

4 REPLIES 4
Ailsa-msft
Community Support
Community Support

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 :

Ailsa-msft_0-1622450538917.png

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.

View solution in original post

Thanks for the detailed explanation @Ailsa-msft !!

amitchandak
Super User
Super User

@rodneyc8063 , Try like

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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 😛 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors