Convert a column that has a data type of integer into date in Cognos report studio

sql-server datetime cognos-10 cognos-bi
BongReyes picture BongReyes · Jul 29, 2015 · Viewed 8k times · Source

I have a column that appear like this and the data type is integer. I get that data from AS400 server that's why it uses integer data type. The date format is represent as YYYYMMDD

enter image description here

enter image description here

In report studio, I created a data item that would convert this integer column to date time. But it failed.

enter image description here

I have tried lots of different approach but none of these worked.

cast([WCPDOD], 'YYYYMMDD')

cast([WCPDOD], date) UDA-SQL-0219 The function "to_date" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.RSV-SRV-0042

cast([WCPDOD], YYYY-MM-DD)

cast([WCPDOD], datetime)

cast_timestamp([WCPDOD], datetime)

cast_timestamp([WCPDOD], date)

cast_integer([WCPDOD], date)

Can someone help me with this? My goal is to get this 20150729 into this 07/29/2015 at least

Answer

Johnsonium picture Johnsonium · Jul 30, 2015

First, cast your 10-digit integer into a string:

Data Item2

cast([Data Item1],varchar(10))

Next, use substring to extract out the date components and build a date string:

Data Item3

substring([Data Item2],1,4) + '-' + substring([Data Item2],5,2) + '-' + substring([Data Item2],7,2)

Lastly, convert the resulting string to date format:

Data Item4

cast([Data Item3],date)

Of course, this can all be done in a single expression but I broke it out here for clarity.

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
How to convert DateTime to VarChar

I am working on a query in Sql Server 2005 where I need to convert a value in DateTime variable into a varchar variable in yyyy-mm-dd format (without time part). How do I do that?

Read More