[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

Handling Null Dates?



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