Firebird V2.1 Error: value exceeds the range for valid dates

You are restoring a backup of a Firebird 1.5 or 2.0 database to Firebird 2.1 and you see an error similar to this:

gbak: writing data for table xyz
gbak:20000 records written
gbak: Error: value exceeds the range for valid dates
gbak: Error: gds_$receive failed
gbak:Exiting before completion due to errors

How do you go about solving the problem?
Well first of all a quick visit to the Firebird bug tracker reveals
http://tracker.firebirdsql.org/browse/CORE-1714 and a couple of comments from Dimitry Yemanov.

“The error means to say that some column has an invalid date value (outside the supported range). Prior to V2.1, it was possible to store such invalid values in the database, but now it's prohibited. A verbose output should point you to a problematic table.” Also “The current behavior is intended and is unlikely to be changed.”

1. Firstly use the –v (verbose option of gbak) to find out the table that is causing the problem.
2. Check the table xyz for date columns
3. Perform the following SQL operation on all the date columns you found in 2.

isql> update XYZ set MY_DATE_COLUMN = MY_DATE_COLUMN;

At some point during the process you will see the same error that gbak produced, but now you will know which column is causing the problem.

4. Install Firebird 1.5 or 2.0, by going back to the version of Firebird that allows for invalid dates, you will be at least able to correct the invalid date to something more appropriate.

5. Now lets check for dates that are outside of their proper range (01 Jan 0001 - 31 Dec 9999)

Below date zero:

isql> select PRIMARY_KEY from XYZ where MY_DATE_COLUMN < '0001-01-01'

if an error occurs correct the date to something more appropriate and meaningful

Maximum date

isql> select PRIMARY_KEY from XYZ where MY_DATE_COLUMN > '9999-12-31'

if an error occurs correct the date to something more appropriate and meaningful

6. You can now backup the database using Firebird 1.5 or 2.1 and successfully restore under 2.1

 For anyone interested, the following is a shell script provided by a friend that can be used on Linux to correct the above errors automagically across multiple databases.

#!/bin/bash -u
# correct OLD invalid dates in a DB
#
#    Ray Holme, July 2013 -  207-583-6613
#       Rainbow Applications Inc., Waterford, ME 04088
#
ARGCNT=$#
MYNAME=`/bin/basename $0`
DATEL="'1/1/0001'"
DATEH="'12/31/9999'"
DRYRUN=0
ERRCNT=0
FLDS=/tmp/$$.flds
ISQL=/opt/firebird/bin/isql
ListDBS=""
LOG=/tmp/$$.out
NOFILE="could not find"
NOTNULL="is not null"
RANGE="not between $DATEL and $DATEH"
RM=/bin/rm
SQL=/tmp/$$.sql
USAGE="usage: $MYNAME <-d> DBname1 ...>"
WORK=/tmp/$$.wrk

ABORT()       { $RM -f $FLDS $SQL $WORK; echo $MYNAME aborted; exit $1; }
INCR_ERRCNT() { ERRCNT=`echo $ERRCNT + 1 | /bin/bc`;  }

ANSWER_ME() {
  OK=2; until [ $OK -ne 2 ];
   do
    echo "Please type '$2' or '$3'."; echo -n "$1 [$2]: "; read ANS
    if [ "$ANS" = "$2" -o "$ANS" = "" ]; then OK=1
    elif [ "$ANS" = "$2" ];              then OK=0
    fi
   done
}

#get list of dbs to do
until [ $# -eq 0 ];
 do case "$1" in
     -d) DRYRUN=1; ARGCNT=`echo $ARGCNT - 1 | /bin/bc`    ;;
      *) if [ ! -f $1 ]; then echo $NOFILE $1; INCR_ERRCNT;
         else ListDBS="$ListDBS $1"                       ;
         fi                                               ;;
    esac
    shift
 done
if [ "$ListDBS" = "" ]; then echo "$USAGE"; exit 1; fi
trap "ABORT 1" 1 2 3

if [ ! -w /etc/passwd ]; then echo you must be root to execute $MYNAME; exit 1; fi
$RM -f $LOG        # get what we need to do (worst case)
for DB in $ListDBS
 do
  $RM -f $FLDS
  if [ $ARGCNT -gt 1 ]; then
    ANSWER_ME "about to check $DB - c to continue, s to skip" c s
    if [ $OK -eq 0 ]; then continue; fi   # skipped
  fi
  $ISQL $DB -pag 9999 > /dev/null 2> $WORK <output $FLDS;
select r.rdb\$relation_name, rf.rdb\$field_name
  from  rdb\$relations r
  inner join rdb\$relation_fields rf on (r.rdb\$relation_name = rf.rdb\$relation_name)
  inner join rdb\$fields f on (f.rdb\$field_name = rf.rdb\$field_source)
  where r.rdb\$view_source is null and r.rdb\$system_flag = 0
    and f.rdb\$field_type = 35
  order by 1, 2;
EOF
  if [ -f $WORK ];     then XXX=`cat $WORK`; else XXX="";            fi
  if [ "$XXX" != "" ]; then echo problems working $DB; INCR_ERRCNT;  continue;    fi
  /bin/egrep -v "RELATION_NAME|==========" < $FLDS | grep -v '^$' \
  | /bin/awk '{printf("%-32.32s %s\n", $1, $2)}'> $WORK
  /bin/mv $WORK $FLDS
  if [ -f $FLDS ];     then XXX=`cat $FLDS`; else XXX="";            fi
  if [ "$XXX" = "" ];  then echo "no dates in $DB - curious (skip)"; continue;    fi
# pass 1 - we show the records with errors embedded
  echo "Listing effected records in DB: $DB"        >> $LOG
  echo "output $LOG;"                     > $SQL
  LastTable=""
  while read TABLE FIELD
   do
    if [ "$TABLE" != "$LastTable" ]; then
      if [ "$LastTable" != "" ]; then echo ";"        >> $SQL; fi
      LastTable="$TABLE"
      echo "select distinct 'table: $TABLE' from rdb\$database;" >> $SQL
      echo "select * from $TABLE where "        >> $SQL
    else echo -n " or "                     >> $SQL
    fi
    echo "( $FIELD $NOTNULL and ($FIELD $RANGE ))"    >> $SQL
   done < $FLDS
  if [ "$LastTable" != "" ]; then echo ";"        >> $SQL; fi
  $ISQL $DB -pag 9999 -i $SQL
# pass 2 - do clean up
  $RM $SQL
  while read TABLE FIELD
   do
    echo "update $TABLE set $FIELD = $DATEL where $FIELD $NOTNULL and $FIELD < $DATEL;" >> $SQL
    echo "update $TABLE set $FIELD = $DATEH where $FIELD $NOTNULL and $FIELD > $DATEH;" >> $SQL
    echo "commit;"                    >> $SQL
   done < $FLDS
  echo "exit;"                        >> $SQL
  if [ $DRYRUN -eq 0 ]; then $ISQL $DB -pag 9999 -i $SQL
  else echo "Would run this sql on $DB"; /bin/more $SQL
  fi
 done                     # end of outer (DB) loop

$RM -f $FLDS $SQL $WORK
echo file $LOG contains a list of all effected records
exit $ERRCNT