| Subject | Re: [firebird-support] COALESCE() + CAST() converts timestamp format |
|---|---|
| Author | W O |
| Post date | 2014-05-13T09:07:18Z |
Svein, Bhavbhuti has a background as a Visual FoxPro developer and in that language exist empty dates.
Probably is the reason why he had casted an empty string as a timestamp.
The same thing had happened to me when I was learning SQL.
Greetings.
Walter.
On Tue, May 13, 2014 at 4:02 AM, Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
>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.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.
>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.
If you're thinking in terms of WHERE clauses, you may sometimes use IS [NOT] DISTINCT FROM as an alternative to = or <>.
HTH,
Set