>Thanks Svein.  Would you suggest I double COALESCE it?  I think it will be an overkill and I might end up with an ANSI datetime again. 
>Or should I not COALESCE it at all?  I wanted a blank date time in case of a NULL for tBillDt and if there is a date in tBillDt I need
>it for date calculation later on as post processing from the front ends.

What is a blank date, Bhavbhuti? Either it is NULL (unknown) or an actual date. Blank as in '' is a (var)char concept, it doesn't exist for dates or timestamps and give an error. So you basically have to choose between having a date for calculation or a string for display.

If you're thinking in terms of WHERE clauses, you may sometimes use IS [NOT] DISTINCT FROM as an alternative to = or <>.

HTH,
Set