COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: gshilpa24 on 20 Sep 2010 05:20:27 AM

Title: Unable to use date functions
Post by: gshilpa24 on 20 Sep 2010 05:20:27 AM
Hi,
I have a date column and i m trying to convert a date using to_char function. The problem is it is not allowing me to do so. I get the follwoing error.

GEN-ERR-0015
Initially, in data source type(s) 'SS', function 'fn:to_char' is not supported in 'RelationalQueryProvider'. After decomposition, in data source type(s) 'SS', function 'fn:to_char' is not supported in 'RelationalQueryProvider'.

Is there any solution to this? Please respond ASAP. If i have to convert in an expression how can do it without using the to_char function???

Thanks,
Sg
Title: Re: Unable to use date functions
Post by: technomorph on 20 Sep 2010 05:42:49 AM
I haven't seen that error message before in relation to Oracle. Silly question, but it definitely is an Oracle data source you're using right?

Anyway, you could try the cast function:

Cast(Exp, DATE)

Cheers
Title: Re: Unable to use date functions
Post by: gshilpa24 on 20 Sep 2010 05:55:05 AM
The source is not oracle. It is SQL Server 2005. It sounds silly to me too when i am unable to use this function   :-X
Title: Re: Unable to use date functions
Post by: gshilpa24 on 20 Sep 2010 06:03:07 AM
here i want to convert the date which is in the format by default sep 20,2007 to mm/dd/yyyy. If i have to convert to a different date format i have to use to_date or to_char function which is not allowing me, even though i try to cast using the CAST function.
Title: Re: Unable to use date functions
Post by: technomorph on 20 Sep 2010 06:17:09 AM
OK, to_date, to_char are native Oracle functions so won't work with SQL Server. Also, depending on what you're doing, if the column is already of a Date data type and you just want to change the format, you might be able to use the Data Format property i.e. of a data item in a list.

If that isn't an option, you could look at the following:

http://www.w3schools.com/sql/func_convert.asp
Title: Re: Unable to use date functions
Post by: cognostechie on 20 Sep 2010 01:58:48 PM
For SQl Server, you can use 'cast_char' function or try this:

convert(char(-8-),<Date Column Name>,112) - This will convert it to character as 20090101.

Instead of 112 , you can also try other numbers. There are different nos for different conversions.
Title: Re: Unable to use date functions
Post by: cognostechie on 20 Sep 2010 02:02:26 PM
I edited the last post because it was showing a smiley in place of char(8). In the convert function, disregard the dashes '-' before and after the number 8. I had to put that to avoid the smiley being posted
Title: Re: Unable to use date functions
Post by: gshilpa24 on 20 Sep 2010 11:26:27 PM
Perfect !It worked.  Thanks a lot cognostechie and technomorph for the help.

:)