Date Conversion - Urgent

[gshilpa24]

Hi Folks,

I have a calculated data item for a column as below:

case
when [StatusName]<>'Withdrawn'
then  (current_date - sltapprovaldate)
when [StatusName]='Withdrawn'
then
(null)
when [StatusName]='Joined'
then filleddate - slatapprovaldate
end

the problem is i need to show value 0 when the status is joined

as of now it gives me values for as 22 days, 43 days and null(blank)

i tried to convert the same to cast_varchar (filleddate - sltapprovaldate) and then give as ('0')
but i get something like 0000000022 00:00:000000 for which i have values,

however 0 gets populated when the statusname = joined but rest of values i get as above

i tried all the cast funstions
nothing seems to work fine.


please help...Thanks.

[blom0344]

case
when [StatusName]<>'Withdrawn'
then  (current_date - sltapprovaldate)
when [StatusName]='Withdrawn'
then
(null)
when [StatusName]='Joined'
then filleddate - slatapprovaldate
end

Did you notice that your case logic will always stop before the 'joined' part.   <>'Withdrawn'  and = 'Withdrawn' covers each and every  possibility, so the 3rd evaluation is never reached!!

Change the logic sequence and use cognos functions by default!!

[gshilpa24]

Thank you very much for the quick response. I didn't understand the use cognos functions by default. Please let me know. Thanks!

[blom0344]

You are subtraction dates as if they where numerical figures. Cognos has a nice inbuild function to calculate date differences. Check it out!!

[gshilpa24]

Thank You. Will check the same.