Thanks! On Nov 28, 2007 10:47 AM, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
> > On Nov 27, 2007, at 21:04 , Gera Mel Handumon wrote: > > > I encounter an error if i use NULLIF with timestamp with time zone. > > eq. dbtime=nullif(mytime,'') > > > > i want to null the value of field DBTIME if the variable mytime=" " > > > > DBTIME ="timestamp with time zone" datatype > > > > error: column DBTIME is of type timestamp with time zone but > > expression is of type text. > > I believe the reason is that '' is not a valid timestamp value: think > of it this way: > > IF mytime = '' THEN > mytime := NULL; > END IF; > > The first thing it needs to do is compare the mytime value with ''. > As '' is not a valid timestamp value, it may be casing mytime to > text. You'll run into problems if you're assigning a text value to a > timestamp field (which happens after the initial comparison--and the > cast--are done.) > > I think you may need to handle this is you middleware, or handle the > IF THEN explicitly in a function. Maybe CASE would work: > > CASE WHEN mytime = '' THEN NULL > ELSE CAST(mytime AS TIMESTAMP) > END > > Michael Glaesemann > grzm seespotcode net > > > -- Gera Mel E. Handumon Application Programmer PaySoft Solutions, Inc. ----------------------------------------------------------------- "Share your knowledge. It's a way to achieve immortality" - Dalai Lama
