Unable to use date functions

[gshilpa24]

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

[technomorph]

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

[gshilpa24]

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

[gshilpa24]

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.

[technomorph]

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

[cognostechie]

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.

[cognostechie]

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

[gshilpa24]

Perfect !It worked.  Thanks a lot cognostechie and technomorph for the help.

:)