Date Format - Data Reader Rejecting Records

Previous Member

Hello,

 

I am currently using a Data Readers to load a cube to provide a count for Work Orders created based on a Date/Time field out of one of our SQL Tables.  The Date Reader is rejecting half of the records due to a formatting error found in the Date/Time field.

 

Below is the current SQL query I am using to load the cube:

SELECT Convert(CHAR(22), dbo.work.datetime, 112), dbo.work.work, dbo.work.customer, 1
FROM dbo.work
WHERE dbo.work.company = '36' and dbo.work.datetime >= '06/11/2018'

 

Is there a particular syntax required to format the Date/Time fields so that no records are rejected?

 

I have also attached a screen shot of the Date Reader results and an example of one of the Data Reader errors found in the XML Log.

 

Any assistance with is greatly appreciate.

 

Thanks!

Ford Reid

Helmut Heimann

Hi Ford Reid,

 

did you check the SQL-log and the SQL-settings in the DataReader as depicted here:

 

 

You could change the format according to your needs...

 

Hope, this helps!

Kind Regards,

Helmut

Brendan Broughton

Hi Ford Reid ,

From my limited knowledge that Char(22) will make your date 22 characters long, but I wouldn't have expected that to cause an issue as with the 112 I would have thought it'd still be in the correct order (but I do have limited knowledge here).

 

If it was me I'd try changing that char() to 6 (if reading to month) or 8 (to day) and see if that made any difference.

Previous Member

Hello Helmut Heimann and Brendan Broughton!

 

The problem was simply the append vs blank option.  Changing to append for the dimensions I was cubing on brought in all records.

 

Thank you both very much for your assistance!

Ford Reid