What Date Format should apply in Expression Definition

[Rizwan]

What Date Format should be in the Expression Definition-
Three columns, Record_ID, Amount and Date are available in the database, and want to add columns for each month, such as Jan amount displays in Jan column and so on. See below as an example.

REC_ID   AMOUNT   DATE   Jan   Feb   Mar   Apr   May
1   $500.00    1/2/2010   $500.00                
2   $400.00    1/20/2011   $400.00                
6   $800.00    2/20/2011       $800.00            
7   $900.00    3/9/2011           $900.00        
8   $200.00    3/20/2011           $200.00        
9   $650.00    4/5//2011               $650.00    
10   $550.00    4/20/2011               $550.00    
11   $610.00    5/1/2011                   $610.00

So, I was trying to add "Query Calculation" with the following Expression Definition for Jan. The "validate" shows no errors, then save and run the query. The Cognos Viewer displays hour-glass is spinning for 10 seconds and then it stops, and if clicking on Cognos Viewer window, the window becomes blank. Tired both Expressions:

Expression#1 for JAN: -
IF ([DATE] between 01/01/2011 and 01/31/2011)  THEN ( [AMOIUNT]) ELSE (' ')

Expression# 2 for JAN:-
IF ([DATE] between (2011-01-01 12:00:00.000) and (2011-02-01 12:00:00.000)  THEN ( [AMOUNT]) ELSE  (' ')

The database date format displays as "2011-01-31 12:00:00.000" for January 31 2011.
And the Data Type is "datetime"

Database: MS SQL Server 2005
IBM Cognos Studio 8
OS:- Windows 7 Professional
Browser:– IE v 8

The report returns data normally with the three columns, somehow the date expression is not working. Any help would be appreciated..
Thanks,

[Rizwan]

Hi, any ideas what date format can be applied in Expression Definition with IF and THEN.. Thx.

[Kanag]

Hi Rizwan,
                Dont know the exact reason behind that failure!

May it be due to the return values ( one is Integer , the other one is character ) ???

Just try giving a number (say 1)  in else part...,

Rgds,
KR

[PRIT AMRIT]

Expression#1 for JAN: -
IF ([DATE] between 01/01/2011 and 01/31/2011)  THEN ( [AMOIUNT]) ELSE (' ')

--Jan--

case
when (cast([DATE],date) between _first_of_month(cast([DATE],date)) and _last_of_month(cast([DATE],date)) )
and extract(month,cast([DATE],date))=1
then [AMOUNT]
end

Repeat the same for rest of the months...