Null date storage

[Peter Bateman]

Harold:

   A 0 value for an InterBase or an Oracle/RDB date is 17-Nov-1858 as that
   is the base date.

   Older versions of PowerHouse put 0 in the field when a date was not 
specified.

   Now you can specify in PDL what is to happen if a date is not specified.

   Also,  in PDL you probably want to say NULL VALUES ALLOWED on your 
database definition.

   After you do that you can fix the data with your database management tool 
or with QTP.

   QTP
   Access Table
   select if dateitem1 = 18581117 or dateitem2=18581117
   Output table update

     item dateitem1 final NULL if dateitem1 = 18581117 else dateitem1
     item dateitem2  final NULL if dateitem1 = 18581117 else dateitem2

  database management tool
-- you may need a to_date function around the 1858117 it has been awhile
  UPDATE table set dateitem1 = NULL where dateitem1= 18581117
  UPDATE table set dateitem2 = NULL where dateitem2=18581117





>From: "Johnson, Harold A EDUC:EX" <Harold.A.Johnson@gems1.gov.bc.ca>
>To: "Powerh-L (E-mail)" <powerh-l@lists.swau.edu>
>Subject: Null date storage
>Date: Thu, 11 Sep 2003 08:53:27 -0700
>
>Just to refresh my memory, but which product is the one responsible for
>storing null dates as 17-Nov-1858 in RDB?  Is it RDB itself or Powerhouse?
>External queries (depending on the product) either show a blank, a null or
>the 17-Nov-1858 date.   What's normally done here?   Can this date be
>modified somehow to show a real null ?
>
>thnx
>
>

_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail