Date Formatting Error - RimsNodeClassification table

Good afternoon,

I have built a script to run a report for all Physical folders to be counted that were closed between 2 user entered dates. It would seem that the system is utilizing the date format of YYYY:MM:DD TT:TT:TT.TTT whereas the RIMSNODECLASSIFICATION.RIMSSTATUSDATE column has the format YYYYMMDD. The error that I am receiving

Content Server Error: Error processing request.

[[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime.

Is anyone aware on how to format the date within the select statement to prevent this error?

Thanks very much.

Dave

Find more posts tagged with

Comments

David_Miehl

Sorry.. Here is the code..

SELECT CS.KUAF.Name "UserName", COUNT(*) "CLOSED COUNT"

from CS.DTree, CS.KUAF, CS.RIMSNODECLASSIFICATION

where CS.DTree.USERID = KUAF.ID and CS.DTREE.UserID = %1 AND CS.DTREE.DATAID = CS.RIMSNODECLASSIFICATION.NODEID AND SUBTYPE = 412 AND RIMSSTATUSDATE >= %2 AND RIMSSTATUSDATE <= %3 AND RIMSSTATUS = 'CLOFERM'

GROUP BY CS.KUAF.NAME

Mukesh_Giri

Hello Assuming you have a SQL Database please try the below code snippet.

Cast(RIMSNODECLASSIFICATION.RIMSSTATUSDATE as Date) between Cast(%2 as Date) and Cast(%3 as Date)

Let me know if that helps,

Mukesh Giri

Quoted David Miehl on 2013-03-05 11:08:

Sorry.. Here is the code..

SELECT CS.KUAF.Name "UserName", COUNT(*) "CLOSED COUNT"

from CS.DTree, CS.KUAF, CS.RIMSNODECLASSIFICATION

GROUP BY CS.KUAF.NAME

David_Miehl

Mukesh,

Thank you very much for your reply. I think that may be part of the solution.. But, now it looks like a CONVERT may have to be implemented as well..

Content Server Error: Error processing request.

[[Microsoft][ODBC SQL Server Driver][SQL Server]Explicit conversion from data type int to date is not allowed.

Thanks,

Dave

Mukesh_Giri

Conversion could be done by using Convert function available something like below but you will have to figure a way to incluide the date ranges for your user inputs %2 and %3.

CONVERT ( varchar ( 10 ), RIMSNODECLASSIFICATION . RIMSSTATUSDATE , 101 ) 'Date'

Thanks,

Quoted David Miehl on 2013-03-05 13:45:

Mukesh,

Thank you very much for your reply. I think that may be part of the solution.. But, now it looks like a CONVERT may have to be implemented as well..

Content Server Error: Error processing request.

[[Microsoft][ODBC SQL Server Driver][SQL Server]Explicit conversion from data type int to date is not allowed.

Thanks,

Dave

Colin_Schmidt

There is at least one other field/column in the LL DB where the DATE is kept in 'dd-MMM-yy' format, but the characteristics of the field is not "date" and so no date-formatting or conversion can take place.

SOVMGMTMSGS.

DATESTR as VARCHAR(32 CHAR)

David_Miehl

My apologies for the very late response. But, I just wanted to thank everyone for their prompt and extremely helpful responses. Your insights and suggestions provided me with the information I needed to create the report needed.

More to come I'm sure!

Thanks again.

Dave