Pamela, la mejor opción que tenés para restar fechas es trabajar directamente con funciones UDF (siglas en inglés para función definida por el usuario), que se crean en un lenguaje como C o C++ y que permiten llevar a cabo tareas que el SQL no provee y que uno desea ejecutar sobre el motor y no sobre el cliente.
Existen muchas librerías que manejan este tipo de problemas (restar fechas).
Yo personalmente escribo mis propias funciones en C / C++ así que para darte una idea, te paso un ejemplo de como manipular fechas:
simplemente compilás la función en firebird (o interbase) y llamás desde el SQL
CODIGO:
/***********************************************
F_DATEPART
***********************************************/
DLLIMPORT int *f_datepart(ISC_DATE *adt_date,const char *as_datepart)
{
struct tm ldt_date;
int li_result = -1;
isc_decode_sql_date(adt_date,&ldt_date);
//año
if (!strcmp(as_datepart,"yy"))
{
li_result = ldt_date.tm_year + 1900; //pq retorna la cantidad de años desde 1900
return (int *) li_result;
}
//mes
if (!strcmp(as_datepart,"mm"))
{
li_result = ldt_date.tm_mon + 1; //pq retorna de 0 a 11 meses desde enero
return (int *) li_result;
}
//dia real
if (!strcmp(as_datepart,"md"))
{
li_result = ldt_date.tm_mday;
return (int *) li_result;
}
//día de la semana
if (!strcmp(as_datepart,"dw"))
{
li_result = ldt_date.tm_wday;
return (int *) li_result;
}
//día del año
if (!strcmp(as_datepart,"dy"))
{
li_result = ldt_date.tm_yday;
return (int *) li_result;
}
return (int *) li_result;
};
/***********************************************
F_DATETIMEPART
************************************************/
DLLIMPORT int *f_datetimepart(ISC_TIMESTAMP *adt_datetime,const char *as_datepart)
{
struct tm ldt_datetime;
int li_result = -1;
isc_decode_timestamp(adt_datetime,&ldt_datetime);
//año
if (!strcmp(as_datepart,"yy"))
{
li_result = ldt_datetime.tm_year + 1900; //pq retorna la cantidad de años desde 1900
return (int *) li_result;
}
//mes
if (!strcmp(as_datepart,"mm"))
{
li_result = ldt_datetime.tm_mon + 1; //pq retorna de 0 a 11 meses desde enero
return (int *) li_result;
}
//dia real
if (!strcmp(as_datepart,"md"))
{
li_result = ldt_datetime.tm_mday;
return (int *) li_result;
}
//día de la semana
if (!strcmp(as_datepart,"dw"))
{
li_result = ldt_datetime.tm_wday;
return (int *) li_result;
}
//día del año
if (!strcmp(as_datepart,"dy"))
{
li_result = ldt_datetime.tm_yday;
return (int *) li_result;
}
//hora
if (!strcmp(as_datepart,"hh"))
{
li_result = ldt_datetime.tm_hour;
return (int *) li_result;
}
//minuto
if (!strcmp(as_datepart,"mi"))
{
li_result = ldt_datetime.tm_min;
return (int *) li_result;
}
//segundo
if (!strcmp(as_datepart,"ss"))
{
li_result = ldt_datetime.tm_sec;
return (int *) li_result;
}
return (int *) li_result;
};
/***********************************************
F_LEAPYEAR
************************************************/
DLLIMPORT int *f_leapyear(int *ai_year)
{
int li_return;
if (*ai_year % 4 != 0)
li_return = 0;
else if ((*ai_year % 100 == 0) && (!(*ai_year % 400 == 0)))
li_return = 0;
else
li_return = 1;
return (int *) li_return;
};
/***********************************************
F_MONTHDAYCOUNT
************************************************/
DLLIMPORT int *f_monthdaycount(int *ai_year, int *ai_month)
{
int li_leap;
int li_return;
li_leap = (int)f_leapyear(ai_year);
switch (*ai_month)
{
case 1: li_return = 31; break;
case 2: { li_return = (li_leap == 1 ? 29 : 28); break; }
case 3: li_return = 31; break;
case 4: li_return = 30; break;
case 5: li_return = 31; break;
case 6: li_return = 30; break;
case 7: li_return = 31; break;
case 8: li_return = 31; break;
case 9: li_return = 30; break;
case 10: li_return = 31; break;
case 11: li_return = 30; break;
case 12: li_return = 31; break;
default: li_return = -1;
}
return (int *) li_return;
};
/***********************************************
F_DATEDIFF
************************************************/
DLLIMPORT int *f_datediff(ISC_DATE *ad_date1,
ISC_DATE *ad_date2,
const char *as_datepart)
{
struct tm ld_date1;
struct tm ld_date2;
long ll_result;
int li_monthdaycount;
int *li_tempyear;
int *li_tempmonth;
//contenedores de resultados
int li_resultday;
int li_resultmonth;
int li_monthoffset = 0;
int li_resultyear;
int li_resulterror = -1;
if (!ad_date1)
{
return (int *) li_resulterror;
};
if (!ad_date2)
{
return (int *) li_resulterror;
};
isc_decode_sql_date(ad_date1,&ld_date1);
isc_decode_sql_date(ad_date2,&ld_date2);
/*calcular el día
If %DAY(DATE2) <= %DAY(DATE1) ;
then %DAY(RESULT) = %DAY(DATE1) - %DAY(DATE2).
If %DAY(DATE2) > %DAY(DATE1) ;
then %DAY(RESULT) = N + %DAY(DATE1) - %DAY(DATE2) ;
where N = the last day of %MONTH(DATE2). ;
%MONTH(DATE2) is then incremented by 1.*/
if (ld_date2.tm_mday <= ld_date1.tm_mday)
{
li_resultday = ld_date1.tm_mday - ld_date2.tm_mday;
}
if (ld_date2.tm_mday > ld_date1.tm_mday)
{
li_tempyear = &ld_date2.tm_year;
li_tempmonth = &ld_date2.tm_mon;
li_monthdaycount = *f_monthdaycount(li_tempyear,li_tempmonth);
li_resultday = li_monthdaycount + ld_date1.tm_mday - ld_date2.tm_mday;
// ld_date2.tm_mon += 1;
}
/*calcular el mes
If %MONTH(DATE2) <= %MONTH(DATE1) ;
then %MONTH(RESULT) = %MONTH(DATE1) - %MONTH(DATE2).
If %MONTH(DATE2) > %MONTH(DATE1) ;
then %MONTH(RESULT) = 12 + %MONTH(DATE1) - %MONTH(DATE2). ;
%YEAR(DATE2) is then incremented by 1.
*/
if (ld_date2.tm_mon <= ld_date1.tm_mon)
{
li_resultmonth = ld_date1.tm_mon - ld_date2.tm_mon;
}
if (ld_date2.tm_mon > ld_date2.tm_mon)
{
li_resultmonth = 12 + ld_date1.tm_mon - ld_date2.tm_mon;
ld_date2.tm_year += 1;
}
/*calcular el año
%YEAR(RESULT) = %YEAR(DATE1) - %YEAR(DATE2).
*/
li_resultyear = ld_date1.tm_year - ld_date2.tm_year;
//ver que parte retornar
if (!strcmp(as_datepart,"d"))
{
return (int *) li_resultday;
}
if (!strcmp(as_datepart,"m"))
{
return (int *) li_resultmonth;
}
if (!strcmp(as_datepart,"a"))
{
return (int *) li_resultyear;
}
return (int *) li_resulterror;
};
/***********************************************
F_DATETIMEDIFF
************************************************/
/*DLLIMPORT int *f_datediff(ISC_TIMESTAMP *adt_datetime1,
ISC_TIMESTAMP *adt_datetime2,
const char *as_datepart)
{
struct tm ldt_datetime1;
struct tm ldt_datetime2;
long ll_result;
int li_resultday;
int li_monthdaycount;
int *li_tempyear;
int *li_tempmonth;
isc_decode_timestamp(adt_datetime1,&ldt_datetime1);
isc_decode_timestamp(adt_datetime2,&ldt_datetime2);
if (ldt_datetime2.tm_mday <= ldt_datetime1.tm_mday)
{
li_resultday = ldt_datetime1.tm_mday - ldt_datetime2.tm_mday;
}
if (ldt_datetime2.tm_mday > ldt_datetime1.tm_mday)
{
li_tempyear = (int *) ldt_datetime2.tm_year;
li_tempmonth = (int *) ldt_datetime2.tm_mon;
li_monthdaycount = (int) f_monthdaycount(li_tempyear,li_tempmonth);
li_resultday = li_monthdaycount + ldt_datetime1.tm_mday - ldt_datetime2.tm_mday;
}
return (int *) li_resultday;
}; */
buena suerte.
