datename function issues

[beaconbiz82]

Hi everyone,

I have been trying to use the datename function to get the name of the month and it keep getting the following error..
An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
When I tried to use the datepart func I get the same error.The db is SQL. the code I tested is datename({mm},2010-01-01) but I get the error mentioned above
Any help would be appriciated.
Thank you

[blom0344]

Try using a true date as the second argument. This works as designed. Feeding it a string or string cast as date) throws the error. No idea why though..

[Gnani]

Hi sridhar,
What is ur database..?

If it is db2 then try using monthname function.
fog eg: monthname(datecolumn)

If it is oracle then try using to_date funtion.
for eg: to_date(datecolumn,'mon')


Gnani..

[blom0344]

The db is SQL.

[Gnani]

I am not sure about the SQL server...
but try the below.

datename({mm},'2010-01-01')

or

datename(month, '2010-01-01')


Gnani...

[jahnavi]

if you are using the SQL Server you can use the extract function to extract the month from the dataitem

[blom0344]

if you are using the SQL Server you can use the extract function to extract the month from the dataitem

extract is a Cognos function and will yield an integer value for the month. You can wrap a case around to translate the 12 values to Monthnames of course..

[beaconbiz82]

thank you eveyone for your response...
blom you are so right.. it did work for the data items directly...and yes the work around for it i have in the report right now is a case statement like u suggested but the expression for the data item is changing and it is going to be a little more complex that just this one case, so i needed that func to work.. thank you so much for that piece of information i really appriciate it... it does not work for current_date either.. it has to be a data item..
once again a big thank you to everyone for their response...
have a great day!!!