Convert to date

[juergen.jaeger]

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

[MFGF]

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

[juergen.jaeger]

Hi,

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

Regards
Juergen

[Tseug]

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

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

[MFGF]

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

[cognostechie]

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.

[juergen.jaeger]

Hey Guys,

Thx for you help!

Regards
Juergen

[juergen.jaeger]

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

[MFGF]

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