Converting empty date value to string '00/00/00' 1

[Rbp1226]

I need convert empty or null value to '00/00/00' in report, the column is date type, if I use function Cstr:

IF CSTR({DT_RT _TO_CIVPAY}) = '' THEN
'00/00/00'
ELSE
Cstr({DT_RT _TO_CIVPAY})

It works fine for empty value but the real date comes format like: 3/5/2007 12: 00: 00 AM (example)

If I use Cdate function it comes up ‘Bad date string format’ error

IF CSTR({DT_RT _TO_CIVPAY}) = '' THEN
Cdate('00/00/00')
ELSE
Cdate({DT_RT _TO_CIVPAY})

Is any one having good solution? Thanks!

 

[lbass]

Try:

If isnull({DT_RT _TO_CIVPAY}) or
{DT_RT _TO_CIVPAY} = date(0,0,0) THEN
'00/00/00' ELSE
totext({DT_RT _TO_CIVPAY},"MM/dd/yy")

You didn't say how you wanted the date formatted, so you might need to change the formatting there.

-LB

 

[Rbp1226]

Hi LB,

It works fine; I did change the formatting as date, please see the formula below. Thanks so much for your help!!

Rbp


If isnull({DT_RT _TO_CIVPAY}) or
{DT_RT _TO_CIVPAY} = date(0,0,0) THEN
'00/00/00'
ELSE
totext(date({DT_RT _TO_CIVPAY}))