Convert Character length 16 to Date data type of query item

[saumil287]

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

[nishant.thakar]

saumil287,
Can you please share the error details?

[saumil287]

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.

[tjohnson3050]

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])

[saumil287]

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

[tjohnson3050]

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))

[saumil287]

Hi Johnson,
Thank you very much
I got it.

appreciated a lot.