Comparing Casted Dates Error?

[norrisar]

Hello there,

I have two date data items that I am trying to compare to see which is more recent. When doing a date function (for example _add_days) on either of them individually, it works fine. Whenever I try one that uses both (_days_between, or a simple > or <) I get an error. I thought maybe the two data items were just a different format, so I tried the following to make them the same:

cast([dataitem1], varchar(10))   followed by
cast((substring([dataitem1varchar], 1, 4) + '-' + substring([dataitem1varchar], 6, 2) + '-' + substring([dataitem1varchar], 9, 2)), date)

&

cast([dataitem2], varchar(10))   followed by
cast((substring([dataitem2varchar], 1, 4) + '-' + substring([dataitem2varchar], 6, 2) + '-' + substring([dataitem2varchar], 9, 2)), date)


This is the error that I receive trying to perfrom _days_between ([dataitem1datecast], [dataitem2datecast]):
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.

UDA-SQL-0460 A general exception has occurred during local processing.UDA-EE-0094 The operation "_days_between" is invalid for the following combination of data types: "date2" and "nvarchar"RSV-SRV-0042 Trace back:RSReportService.cpp(726): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_RequestRSASyncExecutionThread.cpp(828): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(277): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(884): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(670): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(292): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(359): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(309): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(309): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(309): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(359): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSAssembly.cpp(671): QFException: CCL_RETHROW: RSAssembly::createListIteratorAssembly/RSAssembly.cpp(726): QFException: CCL_RETHROW: RSAssembly::createListIteratorRSQueryMgr.cpp(1056): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(1132): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1296): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgr.cpp(1570): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgr.cpp(1560): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(170): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(162): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1145): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1102): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1078): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4469): QFException: CCL_THROW: CoordinationPlanner



I am using Cognos 10.2. Hopefully this makes sense and ANY help would be greatly appreciated!

[yossiea]

What I have found that often works best is the _make_timestamp() function that is built into Cognos. Dates are funky and using that function, it kind of equalizes the records. Just do a year,month,day for both and then you should be able to do calculations.

[norrisar]

That seemed to have worked!! Thank you so much!  :D