Debugging Date Conversion Error

[jburklund]

I am getting the following error when trying to load data from an Azure SQL database in both Excel and PBI Desktop: "OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Conversion failed when converting date and/or time from character string.."

The database has 10 date columns and millions of rows. Is there a way to trace which column is having the conversion error? Better yet, which column and which row numbers? I'm guessing there are special characters in one of the columns that is causing the error but I would like to know which column. 

[BA_Pete · Accepted solution]

Hi @jburklund ,

Whereabouts in your step list does this occur? Is there a specific step that triggers the error or is it immediately at Source/Navigation?

If it's after a certain step, then you may be able to select the step before it, go to the Home tab > Keep Rows > Keep Errors. You could also try inserting steps before the error steps that perform Clean and Trim functions on all of your columns from the Transform tab > Format > Trim/Clean.

If it's immediately on load, then I'd recommend running a query against your server, something like this:

//Column-by-column
SELECT Col1
FROM yourTable
WHERE Col1 like '%[^a-Z0-9]%'

Re-run the query for each column in turn until you hit one (or more) that contains special characters.

Pete

[jburklund]

Hi Pete - it is immediately at load. Can I do this SQL statement within PowerQuery? My SVD does not currently have any software for SQL queries I believe. 

[BA_Pete]

@jburklund ,

Yes, you can create a new blank query, select SQL Server as source type then, when the database dialog comes up, enter you credentials and put your SQL query here:

Once you've run the query for one column, you can just hit the little cog next to the source step in Power Query. This will open this query window back up so you can just adjust the query for your new column(s).

Pete

[jburklund]

Thank you! This should help me find the errors.