Re: Handling Null Dates?

[Gracen Duffield <gduffiel@TDHCA.STATE.TX.US>]

I've had this problem before.
Make sure you don't have a space in the field you are trying to convert to a
date.


 TO_DATE((ltrim(rtrim($TERMINATION_DT,' ')), ' ')),'YYYY-MM-DD'))

Gracen Duffield
Texas Department of Housing and Community Affairs
475-3839


-----Original Message-----
From: Mike Olson [mailto:msolson@FASTENAL.COM]
Sent: Monday, May 17, 1999 9:48 AM
To: Multiple recipients of list SQR-USERS
Subject: Re: Handling Null Dates?


Shawn,
Are you using Oracle's TO_DATE function?  Try adding it to your sql.

        begin-SQL on-error=PS_SQL_Error
              insert into PS_PERSONAL_DATA
                 (EMPLID,
                  NAME,
                  TERMINATION_DT )
               values
                 ($EMPLID,
                  $NAME,
                  TO_DATE($TERMINATION_DT,'<format of the dates in your
text file, ex. YYYY-MM-DD>'))
     end-SQL

Also you might want to do a rtrim on the variable to make sure you get
an empty string.  Hope this helps.

Mike Olson

        -----Original Message-----
        From:   Shawn Robinson
[SMTP:Shawn.Robinson@NOTES.OSIPC.CAN.EDS.COM]
        Sent:   Monday, May 17, 1999 8:59 AM
        To:     Multiple recipients of list SQR-USERS
        Subject:        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