Re: Handling Null Dates?

[Anne-Marie Matula <amatula@OIT.UMASS.EDU>]

I don't have a problem loading null values.
How is the value getting set to null if it's blank?

What I do is:

   Left and right trim the value I am reading from text file (so if there's
not a date it will have a null value)
   Eg:  LET    $um_addr_end_dt          = LTRIM(RTRIM(substr($input
,360,10),{BLANK}),{BLANK})
                                          Note:  {Blank} was defined to be '
'

I can then do an insert
  begin-SQL
      insert into PS_whatever
         (EMPLID,
          END_DT)
       values
         ($EMPLID,
          $um_addr_end_dt)
     end-SQL

Hope that helps!
Anne-Marie

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Anne-Marie Matula
Student Information System Project
Office of Information Technologies
Whitmore Bldg, University of Massachusetts, Amherst
Phone:  413/577-0685
Fax:    413/545-2150


>Date:    Mon, 17 May 1999 09:58:56 -0400
>From:    Shawn Robinson <Shawn.Robinson@NOTES.OSIPC.CAN.EDS.COM>
>Subject: Handling Null Dates?

>I'm attempting to read a text file then do an insert into a
>Oracle (Ver 8,
>NT) table using SQR.  Many fields in the text file are dates.  I do not
>have a problem loading the dates and converting them to native database
>format using the Format-DateTime function, my problems arise
>when my text
>file does not contain a date for a certain field, ie. is
>blank.  Thus I am
>getting the Oracle Error :
>
>SQL Error status: -1847.000000 message:ORA-01847: day of month must be
>between 1 and last day of month
>
>Can I set a SQR variable to null??  It seems like a null
>string '' does not
> work.  What is the best way to handle this situation?
>
>Things work fine if I hard code a null into the Values of the SQL (see
>below), but this of course is not an option, as the variable may have a
>valid date.
>
>  begin-SQL on-error=PS_SQL_Error
>      insert into PS_PERSONAL_DATA
>         (EMPLID,
>          NAME,
>          TERMINATION_DT )
>       values
>         ($EMPLID,
>          $NAME,
>          NULL  )          !$TERMINATION_DT
>     end-SQL
>