Re: Handling Null Dates?

[Shawn Robinson <Shawn.Robinson@NOTES.OSIPC.CAN.EDS.COM>]

Do I have to use a 'dummy' date??  Fields like Termination Date, Last Date
Worked, ReHire Date, shouldn't these be null for active employees??

If I used a 'dummy' date, I guess I could then perform and update query to
set the dummy date to null.  I would prefer to set the date to null the
first time.

Shawn R






Sam Spritzer <SSpritzer@GW.CTG.COM> on 05/17/99 10:29:29 AM

Please respond to SQR-USERS@list.iex.net

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Shawn Robinson/CAMI/EDS/CA)
Subject:  Re: Handling Null Dates?




Shawn,
I had the same situation and my only recourse was to use 1-jan-1900.  This
worked for me since none of the dates in the entire database was that.
Sam

<<< Shawn Robinson <Shawn.Robinson@NOTES.OSIPC.CAN.EDS.COM>  5/17  9:58a
>>>
Probably an easy one, but the help would be appriciated.

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

Thanks
Shawn R