format number into date

[MWSC]

Hi,
would like to know how i can format a number into a date...

user Inputs a number in yyyymm format eg - 200501
i need to display in my report that this number actually represents January 2005. coz i am using this as a title.
anyone got any idea how to do this?

[Darek]

What is your DB ?

[Darek]

If your DB is M$ SQL it is going to be a three step process.

1. Add a new vendor specific function to SQLServerTree.xml
         <function>
            <id>sql_datename</id>
            <name>datename</name>
            <canonical>datename</canonical>
            <returnType>string</returnType>
            <parameter>
               <type>literal</type>
            </parameter>
            <parameter>
               <type>date</type>
            </parameter>
         </function>
2. Restart CRN
3. Add new calculation to your query
  datename({month},?Par1?+'01')

If it is ORACLE, you can do same with a combo of to_char and to_date.

[Merri]

An alternative way to handle these date display conversion is to have a Date dimension table in your database.
The date dimension table can hold day names, year, month, accounting period, different display formats etc for each date, allowing you to convert between formats with a simple lookup.

[MWSC]

Darek, i'm using Oracle...

i'm really unsure how to do this conversion. you mean i need to specify in my sql statement when i create my model to convert this user input into a date?

my sql is
Select * from table where datemonth = #prompt('prmMonth')#

so i created a prompt page.
user keys in 200501 (data in my database for this is Integer not date)

so how do i convert this user input to dae eg- january.
i know how to use the to_char and to_date.
but i just dont know how to implement it in Cognos...

[Darek]

Oh, that's much easier. It sounds like all you need is conversion of int to date to dispaly on the report only. There is couple possible solutions, one mentioned above with the lookup table. Another one would be to leverage parameter maps. And last but not least is a simple conditional case or if-the-else with left and right or substring functions.

Do you still need an example?

[phani1979]

hai
u can  have the sql funtion as mentioned above to_char, to_date, but as they said,, u need to have tables for data&time format..
for that u need to go for query explorer...
in tabular model section of date& time columns select sort acendint and decending and dont sort option r there. u, select the that column in the tabular section .. in the properties.. u saya s particular month  click as sortasecndint and  another one  in fact item  clcik dont' sort..
try these
sudhakar