>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