PJC Canned reports input screen date format problem

Hi,

Using Sage 2022 on Windows 2019 server.

I am facing a problem with running PJC canned reports in Intelligence reporting. This is with the dates in the filter screen that comes up as soon as I run the report. Please note that I am using European date formats.

Description : [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.

Number : -2147217913

Source :

Driver Error : 241

SQL State : 22008 

The problem is with two main reports, PJC Profit Analysis and PJC Detailed costs that reference dates in the format of 01 August 2022 

Other reports such as PJC Transaction History that reference dates in the same format, run just fine.

I tried playing around with the configuration on Long date with no success.

Any ideas?

Marios

Parents
  • Check the field in SQL to see if there are any values other than the normal Sage300 dates such as a 0.  I've had issues when looking at dates the field is not a required field and users do not always fill it in.  The data defaults to a 0 and I get the above error.  It may not apply in your case but worth a look.

  • Echoing what Terry said... typically there are 0 or null values in the date field.   

    For example, in ICILOC the Last Used date is null (if never used) so you've to deal with it in your SQL statement:

    (ok, this board won't let me paste code, I've no idea why.... always gives an error... frustrating...)

    How about SQL as an image....

Reply
  • Echoing what Terry said... typically there are 0 or null values in the date field.   

    For example, in ICILOC the Last Used date is null (if never used) so you've to deal with it in your SQL statement:

    (ok, this board won't let me paste code, I've no idea why.... always gives an error... frustrating...)

    How about SQL as an image....

Children
No Data