Date Manipulation

[Thermos]

I'm new to cognos and cant figure out how to do something that should be quite simple.  I want to get the first day of the month from a date from my stored procedure.  I have tried cognos syntax, sql syntax, setting the connection to Limited Local, setting the connection interface to OR instead of OL (saw this on a post and have no idea what it would even do), and anything else i could find in the past few days.  I just want to get the month from the procedure data for grouping, graphing etc.  Below i have some functions i have tried but all come back with the same error.  The function "insertfunctionnamehere" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported

I have tried....
month([fieldname])

_first_of_month([fieldname])

cast(convert(char(10),convert(char(2), datepart('MM',[fieldname]))+ '/01/' +convert(char(4), datepart('YYYY',[fieldname])), 101) as datetime)

string2date(convert(char(10),convert(char(2), datepart('MM',[fieldname]))+ '/01/' +convert(char(4), datepart('YYYY',[fieldname])), 101))

Please help!!!  I have googled and searched forums and am completely out of ideas.  Thanks.

[kaevne]

You want to extract the month?  I would just brute force it in Cognos by substring the datetime for the month numbers, then parse that through a case statement to output January, February, March, etc.

[Thermos]

I tried a substring to see if that would make any difference.  It did make it a step farther as it tried to run the report and got to and past the parameters but it failed with An error occurred while performing operation 'sqlOpenResult' status='-28'

So now i dont understand why i am getting this message.  And i dont understand what was wrong with all my previous attempts.  Please Help!

btw, the command i tried this time was...
substring(cast([fieldname] as char), 1, 2)

[DSR]

Hi Thermos,

Just Try below one may give you solution.

To_Char([Field_Name],'mm/yyyy')

Then concatenate this with 01 at the starting or as per your date format.

Good Luck... ;)

Sanjeev

[Thermos]

Thanks for the reply, but i get the same Local Processing error.  The syntax check is fine but it fails when i try to run it.

to_char is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported

I dont believe this is a syntax issue, there must be a setting wrong somewhere.  Any other ideas?

[kulkarni]

Thanks for the reply, but i get the same Local Processing error.  The syntax check is fine but it fails when i try to run it.

to_char is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported

I dont believe this is a syntax issue, there must be a setting wrong somewhere.  Any other ideas?

Try enabling 'Local Processing' on the queries property and see if it helps.

[Thermos]

ok, I set the query properties:  Processing = Limited Local and Rollup Processing = Local

and with this set one thing works.  _first_of_month ([fieldname])
but everything else i try fails, such as: datepart, cast, to_char, substring, etc.  so it appears that i can use any of the "Business Date/Time Functions" with the way i have things set, but if i need to do anything else I'm out of luck. 

what should i set things as to be able to use the other available functions in cognos?

[kulkarni]

You should be able to play with the values of those 2 and get your desired results.  Not sure what is going on in your case.  Perhaps, start off with just your date item in your query and work to get the 1st day of month.  Once you have succeeded then proceed to add other items.

I am looking at your orginal post and you mention that you have a stored proc, from which you are trying to extract the 1st day of month.  Have you considered modifying either your SP or FM (Framework Manager) to calculate what you want and then use it Report Studio?

[Thermos]

I think i have enough working that i can fix this one problem, but i Really need to know why i can't do a dateadd, datepart, etc.  I get local processing is not available as a built-in function every time for those. 

Kulkarni, thanks for you help and i realize i can do some of this in the procedure but i need a cognos fix.  i can't go back and add fields to SP's every time i need some cosmetic work for a report date header or something like that.