Hi All
I created a query item which is concatination of Day,month and year query items.
The created new query item has character length 16 as data type.
I want to change the same in Date datatype
I used the cast funct and also the to_date func
But it is throwing an error.
I am using csv file as a data source
(
.[Attendance].[AttendanceDay] || '-' ||
.[Attendance].[AttendanceMonth] || '-' ||
.[Attendance].[AttendanceYear])
the datatype is character length 16
Your guidance is needed
saumil287,
Can you please share the error details?
Hi
the error which I get when using the cast func on above query definition
RQP-DEF-0177 An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'.
UDA-SQL-0144 An arithmetic exception was detected.
UDA-EE-0029 A date/time value is invalid.
UDA-SQL-0460 A general exception has occurred during local processing.
UDA-CUR-0000 Unable to fetch the row.
When I individually cast the query item in expression for integer.
and then cast the whole to date.
It is giving me unmatching data type char and integr
thanks.
In order to use the concatenate function (||) all of the items must be some form of character data type, not integer.
What you should use is the Cognos make timestamp function
_make_timestamp([xxx].[Attendance].[AttendanceYear],[xxx].[Attendance].[AttendanceMonth] ,[xxx].[Attendance].[AttendanceDay])
Hi
I tried the same but giving an error.
BMT-MD-0003 UDA-SQL-0460 A general exception has occurred during local processing.
UDA-EE-0094 The operation "_make_timestamp2" is invalid for the following combination of data types: "character, character" and "character"
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 "_make_timestamp2" is invalid for the following combination of data types: "character, character" and "character"
I am using csv files as data source.
Database processing is set to limited local
Data type of Year, month and day is character length 16.
Your guidance is appreciated
Try casting each one to an integer inside the make timestamp function
_make_timestamp(cast([xxx].[Attendance].[AttendanceYear],integer),cast([xxx].[Attendance].[AttendanceMonth],integer),cast([xxx].[Attendance].[AttendanceDay],integer))
Hi Johnson,
Thank you very much
I got it.
appreciated a lot.