Re: Handling Null Dates?

[Tony DeLia <tdelia@EROLS.COM>]

Hi Shawn,

   You don't need to use a 'dummy' date (if the date is nullable)...
Here's a quick trick (for ORACLE) you can use for loading dates from a
text file... I'll use MMDDYYYY format for this example...

while 1 = 1

   read 1 into $rec:100
   ...
   let $I_date = rtrim(substr($rec,11,8),' ')    ! MMDDYYYY format
   ...
   do Date-Conv($I_date, $O_date)
   ...
   do Insert-Row                                 ! Insert uses $O_date

end-while

begin-procedure Date-Conv($I_date, :$O_date)

let $O_date = ''                                 ! Date initially null

begin-select On-Error=Ignore-Error

to_date($I_date,'MMDDYYYY')                        &conv_date

   let $O_date = &conv_date

 from dual

end-select

end-procedure

begin-procedure Ignore-Error

! Procedure does nothing...

end-procedure

The procedure will INSERT either valid dates or nulls... You need to use
a 'dummy' date if the date field cannot accept nulls...

                     Tony DeLia


Shawn Robinson wrote:
>
> 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

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com
http://www.sqrtools.com