Expression.Error: The number is out of range of a 32 bit integer value. Convert an Number to a Date

[Anonymous]

Hi there.  

Our SQL guys land dates in the form of text.  They use the following SQL script to generate the date.

SELECT

Termination_order_final_termination_date

,DATEADD(SS,CAST(SUBSTRING(CAST([Termination_order_final_termination_date] AS VARCHAR),1,10) AS BIGINT),'19700101') as Termination_order_final_termination_date_converted

From [LANDING].[LND_QBS_RAT_Replacement_Orders]

I converted the the text string to a date in Power Query by using the following M Script:  

"Date", each if [Termination_order_final_termination_date] = "" then null else Date.AddDays(#date(1970,01,01),Number.FromText(Text.Start([Termination_order_final_termination_date],10))/86400))

Most of them convert correct except the negative Dates.  I tried Number.Abs, however that does not work.

In the SQL script they do not result in Errors.  Please can someone Help me.

I think it is something to do with the BIGINT thing.

[Vijay_A_Verma · Accepted solution]

Actually Date.AddDays expects an integer number. If you want date only, then following should be there for Date.AddDays

Date.AddDays(#date(1970,01,01),Number.IntegerDivide(Number.FromText(Text.Start([Termination_order_final_termination_date],10))/86400,1))

One more point is that when you extract 10 length from negative number, it will have minus sign + 9 digits....So, if you want to extract 10 digits from negative numbers also, then you will need to adjust your formula appropriately.

[ImkeF]

Hi @Anonymous ,
in the Date.AddDays-function, the number must be a whole number. So if you round it, you will get a date.
But if you want to keep the DateTime part of it as well, you can convert first convert the date to number and then use a plus sign to calculate the new datetime:

DateTime.From( Number.From( #date(1970,01,01)) + ,Number.FromText(Text.Start([Termination_order_final_termination_date],10))/86400))