Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nimai123
Post Patron
Post Patron

We couldn't parse the input provided as a DateTime value..

OLE DB or ODBC error: [DataFormat.Error] We couldn't parse the input provided as a DateTime value..

 

My data source is SAP BW 

 

My date column is in text and when i convert it to date it gives me the above error.

 

I tried splitting and then merging back the column but didn't seem to work out.

 

Thanks.

@BhaveshPatel 

 

 

2 ACCEPTED SOLUTIONS
tex628
Community Champion
Community Champion

You should be able to convert text formatted date's to dateTime without any issue so there is something trong with the text. It might be that it's the wrong delimiters or that there are other blankspaces present in the date. If you provide an image of the dates I might be able to see if there are any issues directly. 

Otherwise what you can do if you really can't get it to work is splitting the text column up in year, month and date and use this custom column to create a date: (Make sure that all three columns are formatted to whole number)

#date([Year],[Month],[Date]) as date


Br,
J




Connect on LinkedIn

View solution in original post

7 REPLIES 7

Hello @amitchandak 

 

Thanks for the informative links.

 

In my scenario, I cannot replace the error values as I need the rows as the sales data with date and I also tried changing the Locale which also didn't help!

 

Are there any other options I can try!

 

Thanks!

 

tex628
Community Champion
Community Champion

You should be able to convert text formatted date's to dateTime without any issue so there is something trong with the text. It might be that it's the wrong delimiters or that there are other blankspaces present in the date. If you provide an image of the dates I might be able to see if there are any issues directly. 

Otherwise what you can do if you really can't get it to work is splitting the text column up in year, month and date and use this custom column to create a date: (Make sure that all three columns are formatted to whole number)

#date([Year],[Month],[Date]) as date


Br,
J




Connect on LinkedIn

Hello @tex628 

 

I have applied the solution which you provided but in my scenario, I cannot create a custom Column as I need to implement Incremental Refresh on the Date column which should be the source Date column and not the Custom Column.

 

The Date Column looks like below

Screenshot (158).png

 

@amitchandak 

@parry2k 

tex628
Community Champion
Community Champion

Is the date you're showing there the 4th of june or the 6th of April?


Connect on LinkedIn

Hello @tex628 

 

IT is in dd.mm.yyyy format. 

 

Thanks

tex628
Community Champion
Community Champion

Then i believe thats the issue. A text value date in the format xx.xx.xxxx is read as mm.dd.yyyy for me, meaning that every date with a day above 12 will return an error. 

I'm going to be perfectly honest with you, I don't know if it's possible to change this by toggling the locale. Due to the SAP datasource I'm assuming the process of adding another column to the source is a little more than a 30min tweak?

/ J 


Connect on LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.