CODEWITHSUNDEEP
sql-serverdatetimecognos-10cognos-bi
BongReyes
BongReyes

Reputation: 205

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

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

Upvotes: 1

Views: 11676

Answers (2)

Johnsonium
Johnsonium

Reputation: 2005

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.

Upvotes: 3

FutbolFan
FutbolFan

Reputation: 13733

In SQL Server, you can convert the integer field to varchar and then to a date and then use the date style 101 to achieve your desired format:

DECLARE @datevalue int = '20150729';

SELECT convert(varchar(10),cast(cast(@datevalue AS varchar(10)) as date), 101);

Upvotes: 1

Related Questions