Issue facing in FM while converting dates

[krishdw85]

CASE WHEN(to_char(sysdate, 'Mon DD, YYYY  HH:MM:SS PM') IS NULL) THEN NULL ELSE ('Updated on '||to_char(sysdate, 'Mon DD, YYYY  HH:MM:SS PM')) end

Thanks for your help

[MFGF]

CASE WHEN(to_char(sysdate, 'Mon DD, YYYY  HH:MM:SS PM') IS NULL) THEN NULL ELSE ('Updated on '||to_char(sysdate, 'Mon DD, YYYY  HH:MM:SS PM')) end

Thanks for your help

Go on. Give us at least a little clue. What issue do you have? An error message? The wrong result? No result? We could guess if you like, but there are so many issues you may have it would be pretty pointless...

MF.

[Lynn]

Pretty darn sure that sysdate won't ever evaluate to NULL no matter what to_char formatting you apply to it, so you'll always be falling into the "else" portion of your case statement....

If you are using Oracle TO_CHAR, the link below may help you with format models. For example, MM gives you months, not minutes so HH:MM:SS will give you hours, months, and seconds. Using MI will give you minutes.

Now I'm all out of guesses as to what your issue might be.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34510

[krishdw85]

Am working on FM::

Yes allways sysdate is something i know.......

we have a data with unknown also ......when there is date i have to go into the else condition and i have to concat some text also ""Updated on""


CASE WHEN(to_char(sysdate, 'Mon DD, YYYY  HH:MM:SS PM') IS NULL) THEN NULL ELSE ('Updated on '||to_char(sysdate, 'Mon DD, YYYY  HH:MM:SS PM'))

want to see the data in this format(timestamp) am getting error as to_char will not be usefull for local processig like wise.....

Thanks for your time

[Lynn]

For one thing, there is no point in converting any date to a particular format just to determine if it is null. The condition should just see if it is null or not. And if there is a data item date then why are you using sysdate?

The local processing message means the expression is being evaluated locally on the Cognos server and not in the database, so you can't use database functions like TO_CHAR. You need to write the query so that it processes on the database side or else switch to local functions instead of database functions. Hard to say what is causing local processing in your case, but you could try referencing a database item instead of sysdate and/or using sysdate in curly braces if sysdate is really what you need.

Code Select Expand


CASE WHEN [Namespace].[QuerySubject].[DataItemDate] IS NULL
  THEN NULL
  ELSE 'Updated on ' || TO_CHAR([Namespace].[QuerySubject].[DataItemDate], 'Mon DD, YYYY  HH:MI:SS PM')


Also, there is a separate section for FM related topics so might be better to post there for FM related questions instead of the Report Studio section.

[krishdw85]

Thank you so much

[krishdw85]

still the same

[Lynn]

Details ???

What exactly does the expression look like now? Are you testing the entire query subject in FM and getting the error? Does the error occur when you test the individual data item? Can you view the generated SQL? Are you using local Cognos functions anywhere else in the expression or in the query subject? Is sysdate really a part of your logic or are you just using that for illustration purposes?

It will be difficult to get a good answer if you don't explain the question and the situation well. My guessing so far has not been of any use.

[krishdw85]

Thank you so much i solved

[MFGF]

Would you mind sharing your solution?  It may be of help to others who have a similar question in future.

MF.