Calculating difference between dates in YYYYDDMM format? - in Power Query

[edhans]

I have a database where the dates are integers in YYYYMMDD format - 20191231 is Dec 31, 2019.

I obviously cannot just subtract, because 20200101 (Jan 1, 2010) - 20191231 should be 1 day, but it is 8,870.

I'm trying to find a way to do math to add/subtract days without converting to dates. The reason is any conversion breaks query folding. The cleanest way I know to do it is:

Date.FromText(Text.From(20191231))

That breaks folding in half a second.

But these dates have been used for decades by systems and I am just wondering if anyone knows a formula or way date math was done with them. Or have the apps that use dates in this format always parsed the data out into YYYY MM DD then converted to a date internally?

EDIT: Just want to clarifiy. I did not mark the below answer as a solution. It does not answer the question. If you are trying to generate a query on Power Query and you use the answer below, it will not work without breaking folding, which was the point of my question. 

In fairness, I am not sure there is a solution. It seems PQ doesn't support what I was trying to do, and sometimes the answer is, there is no answer. It isn't to have some forum admin come in and mark something as a solution that is misleading.

[MFelix]

Hi @edhans ,

Not really sure if I understand your question but try to add a calculated column using you code but making some changes:

Duration.Days(  Date.FromText(Text.From([Column2])) - Date.FromText(Text.From([Column1])))

can you share some more data around your issue what do you exactly want to achieve.

Regards,

MFelix

[edhans]

Yeah, I could use a calculated column. I guess my question is more theoretical. For example, systems that use the Julian Date system have a whole set of formulas around which it operates, and being plain math, that will fold in a SQL statement, so 1 row or 1 million, the server does all the work. 

I was trying to find out if anyone had experience with the YYYYMMDD format, which is kind of common, and how people did date math with it. But everything I look up is by converting it to a real date first. Even in SQL, but Power Query won't generate the SQL statements in folding to do that work.

There may not be an answer short of true date conversion. But I figured there were a lot of people here working on various systems and might know how DBAs or programmers dealt with it.

Of course, if anyone can convert YYYYMMDD to a true date in Power Query that will fold, that would solve all of my problems. 

[MFelix · Accepted solution]

Hi @edhans ,

If you want to convert the YYYYMMDD on the SQL query before uploading on PBI you can use the SQL function

CONVERT(CHAR(10), COLUMN, 120)

If you want to do it directly on PBI the best way is to create the calculated column an then delete the text column, for Power Query  as you know what is considered in the model is the latest table on your query so the additional columns that you delete won't show up or make "weight" on your model.

Regards,

MFelix