Print Page - Convert to date

Title: Convert to date
Post by: juergen.jaeger on 03 Feb 2011 12:13:31 AM

Hi all!

I need some help!

One field in my reports constains a serial number with 9 characters. Only numbers.
Now I have to cut the last three characters and the remaining 6 character have to be converted to a date.
for example:

070808115 07.08.08

Working on Cognos 8.2 Report Studio

Thanks
Juergen

Title: Convert to date
Post by: MFGF on 03 Feb 2011 05:24:58 AM

Assuming this is a numeric field, mathematically dividing by 1000 then rounding down with the floor() function will get you the first six digits. You can then split this into year, month and day numbers by dividing by 100 and using the mod() function to get the remainder (once for day and again for month - then use floor() to get the year). Once you have these, you can use them in a _make_timestamp function.

Can't show an example just now as I'm on the train using my iPhone, sorry.

MF.

Snet form my fumblefingers
iPhon 5 usig Tapatalk

Title: Re: Convert to date
Post by: juergen.jaeger on 03 Feb 2011 07:29:51 AM

Hi,

Thanks a lot!
I will test this tomorrow morning and give you a feedback!

Regards
Juergen

Title: Re: Convert to date
Post by: Tseug on 03 Feb 2011 08:55:43 AM

This is certainly not as fancy, but you could also do this:

substring([number],1,2) + '.' + substring([number],3,2) + '.' + substring([number],5,2)

Title: Convert to date
Post by: MFGF on 03 Feb 2011 01:07:51 PM

Quote from: Tseug on 03 Feb 2011 08:55:43 AM
This is certainly not as fancy, but you could also do this:

substring([number],1,2) + '.' + substring([number],3,2) + '.' + substring([number],5,2)

Good point! If your original value is a string rather than a number, and you do not require a real date result, this would be an easier solution.

MF.

Snet form my fumblefingers
iPhon 5 usig Tapatalk

Title: Re: Convert to date
Post by: cognostechie on 03 Feb 2011 02:34:55 PM

Let's say the name of your column that has 9 characters is ChrDate.

cast(substring(ChrDate,1,2) + '/' + substring(ChrDate,3,2) + '/' + substring(ChrDate,5,2),date)

This will conver it to a Date and then use the Format property to display the date whichever format you want to.

Title: Re: Convert to date
Post by: juergen.jaeger on 04 Feb 2011 02:36:08 AM

Hey Guys,

Thx for you help!

Regards
Juergen

Title: Re: Convert to date
Post by: juergen.jaeger on 04 Feb 2011 03:59:51 AM

Ok, now I have another problem.
I tried to use the _days_between function for the date we have generated before

if ([Num of Char]=9)
then
(substring([Abfrage1].[serial number],1,2) + '.' + substring([Abfrage1].[serial number],3,2) + '.' + substring([Abfrage1].[serial number],5,2))
else
(substring([Abfrage1].[serial number],1,2) + '.' + substring([Abfrage1].[serial number],3,2) + '.' + substring([Abfrage1].[serial number],5,4))

and a second date out of the system.

But it looks like I have to convert our self generated date.

the following error occured:

The operation "_days_between" is invalid for the following combination of data types: "varchar" and "date2"

How do I have to convert the data?

Sorry, but I'm new in the Cognos World.

Regards,
Juergen

Title: Convert to date
Post by: MFGF on 04 Feb 2011 04:40:35 AM

From the looks of your expression, you are creating a string that looks like a date, rather than a real date. You will either need to use the _make_timestamp approach, or the cast() approach - both of these will produce a real date which you can then use in your days_between expression.

Regards,

MF.

Snet form my fumblefingers
iPhon 5 usig Tapatalk

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: juergen.jaeger on 03 Feb 2011 12:13:31 AM