Converting integer to date

[fml55555]

Hi Gurus,

I imported a key from a database (SQL) into the FM. This key is integer. The key actually represents a date. I want to use this as a date in the model and reports. Right now, I am getting the following error when I try to compare it with a date (say current_date)

UDA-SQL-0114 The cursor supplied to the operation "APICursor::OpenResult" is inactive.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Arithmetic overflow error converting expression to data type datetime. (SQLSTATE=22003, SQLERRORCODE=8115)RSV-SRV-0042 Trace back:RSReportService.cpp(729): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(848): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(305): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(904): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(591): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(323): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(417): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(178): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(367): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(314): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(367): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(367): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(151): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(417): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSAssembly.cpp(677): QFException: CCL_RETHROW: RSAssembly::createListIteratorAssembly/RSAssembly.cpp(732): QFException: CCL_RETHROW: RSAssembly::createListIteratorRSQueryMgr.cpp(519): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(586): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(678): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(279): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrBasic.cpp(269): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(170): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(162): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1153): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1151): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1108): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1084): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4473): QFException: CCL_THROW: CoordinationPlanner

I have already tried using cast() function in both FM and report studio to convert it into date but it is not working.

The query item in FM is also int.

[Francis aka khayman]

how about converting the integer into a date in SQL Server, then import to FM?

[MFGF]

I have already tried using cast() function in both FM and report studio to convert it into date but it is not working.

Hi,

What does "it is not working" mean? What expression did you use when you were attempting to cast? What format is the integer value in?

MF.

[BigChris]

Apologies for sticking my oar in without any kind of solution, but...wouldn't you need to split the integer into the year, month and day elements before it can do the conversion? Taking the worst case scenario, you'd surely need to specify whether 070409 represented 7th of April 2009, 7th of July 2009, 9th of April 2007 etc...

[Lynn]

I have also found that working with integer representations of dates sometimes requires manipulation to avoid cast errors. For example, zero may be stored when there is no date which obviously can't be casted to date without some alternate logic. In another case the day portion might be zero so that 20140600 indicates June but no specific date. This also obviously can't be casted to date without alternate logic to account for it.

Of course as muppet and BigChris have pointed out, there are some details lacking to really know what advice is going to work.

[fml55555]

Hi Gurus,

Thank you so much for all the replies.

khayman
I do not have access to the server.

Lynn
Thank you for the insights

MFGF
Sorry about not being more specific. Will post error message shortly

I am considering an alternate approach.

My basic requirement is that I have to compare current months last day with the key I mentioned. I am now considering convertion current date (using the curre_date) function into int by using cast function (and of course changing the format to the format of int field which is YYYYMMDD). Do you think its a good approach?

[Lynn]

You are forgetting about BigChris' comment...you need to determine the format of your integer date. I do think sticking with integer makes sense because if there is indexing in place on your database for that integer date you will likely lose performance by casting the int to a date.

If your integer date is absolutely in the format of YYYYMMDD and you are certain there are no zeros or bogus date values then the below expression should give you the integer representation of last day of current month.

Code Select Expand


( extract(year, current_date) * 10000 )
+
( extract(month, current_date) * 100 )
+
extract(day, _last_of_month(current_date) )


[fml55555]

Thank you very much Lynn. I was going to try the exact same thing. Will let you know how it went. I am pretty sure there are no bogus values...