the conversion of a marcher data type to a dateline data type resulted in an outrage value

[pstewart1977]

Hi

I have been pulling in 5 columns of date data from a datawarehouse to views and converting the date information from int to varchar8 then to datetime in views before importing to powerbi. This has been working ok for sometime but recently I started getting the message when refreshing the data in desktop or from the enterprise gateway: the conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The data is pulled in and converted from datetime to date in the report. Can anyone help as the data in the views looks ok? I use CAST(CAST(ShippingDateRequested_SK AS VARCHAR(8)) AS DATETIME) AS [Shipping Date Requested] to convert from int to date time in the SQL views.

Thanks

[pstewart1977]

Hi I'm bringing 5 date time columns from the DWH to views. The original format is in int and i use the below to convert to datetime. The vies execut without problems.

CAST(CAST(ShippingDateRequested_SK AS VARCHAR(8)) AS DATETIME) AS [Shipping Date Requested],

Recently when i pull the datetime date in to power bi i've suddenly started getiing this error: the conversion of a marcher data type to a dateline data type resulted in an outrage value

Has anyone come accross this? or can anyone advise on what to do? as I've pretty much tried everything as a work around.

[GilbertQ]

hi @pstewart1977

Firstly have you updated the On-Premise Gateway to ensure that it is using the most recent version?

Secondly what if you rather do not do the conversion in the Query, but simply import the data as it is in the DWH?

Then also import your Date table and create a relationship in the Power BI Model?

In doing so you can replicate what is in your DWH, and possibly avoid all the issues you are experiencing.

[pstewart1977]

Thanks for the reply. The report was refreshing ok until we used the on premises gateway to do this for the first time. I will look in to updating the gateway asap and will also see if i can remove the date change in power bi.

[pstewart1977 · Accepted solution]

The issue was not with power bi but more to do with the view conversions to do with datetime from INT. Changed the below type statements throughout the views to fix.

CAST(CAST(ShippingDateRequested_SK AS VARCHAR(8)) AS DATETIME) AS [Shipping Date Requested],