DateDiff SQL between two date integers

Power Query Data Setup
1

Hi there, I just completed the SQL for Power BI users… super helpful, thanks.

I have an question, I hope it is OK to post here.

I have written an sql query and pulled two date columns, but they are not actually dates, they are both integers formatted like this yyyymmdd. I want to work out the number of days between them. Therefore have tried a bunch of stuff with CONVERT and CAST in order to get them into a date format so I can correctly work out the number of days.

It is not working though, so I am clearly doing something wrong.

If anyone could help it would be much appreciated.

2

Hi @lvlandmark

If you cast it as a varchar first, then a date it should work for you. Such as this:

DECLARE
	@Date1 int = 20220618
	,@Date2 int = 20220619

SELECT
	CAST(CAST(@Date1 as varchar(10)) as date)
	,CAST(CAST(@Date2 as varchar(10)) as date)
	,DATEDIFF(day,
		CAST(CAST(@Date1 as varchar(10)) as date),
		CAST(CAST(@Date2 as varchar(10)) as date))

That should give you 1 as it is comparing 18th June to 19th June.

1 Like
3

Hi @lvlandmark, did the response provided by @jamie.bryan help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

Thank you so much for your help Jamie, I will give it a go.

5

Hi @lvlandmark, just checking if the suggestion given above helped solve your query. If yes, kindly tag the post as “solution”.

Thanks

6

Hi @lvlandmark, we noticed that no response was received from you.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.