DATE QUERY

[shaunk]

I am running V8.0 against SQL SERVER 2000.

I am using the following formula to determine what quarter a case was referred.

IF {SERVICE_REQUEST.received_date} IN CALENDAR1STQTR THEN 1 ELSE
IF {SERVICE_REQUEST.received_date} IN CALENDAR2NDQTR THEN 2 ELSE
IF {SERVICE_REQUEST.received_date} IN CALENDAR3RDQTR THEN 3 ELSE
IF {SERVICE_REQUEST.received_date} IN CALENDAR4THQTR THEN 4 ELSE 0

{SERVICE_REQUEST.received_date} is of type Datetime (8).

The formula always returns 0 ( the default condition).

Do I need to convert this date before referring to it in the compare.

 

[Tomorrow]

The Calendar1stqtr function refer to the current calendar year only. If you data is dated prior to this year you will get 0.
The select statement should possibly read
if month(inputdate) in [1,2,3] then 1 else
if month(inputdate) in [4,5,6] then 2 else
if month(inputdate) in [7,8,9] then 3 else
if month(inputdate) in [10,11,12] then 4 else 0