Problema con consulta super complicada y fechas

[gnzsoloyo]

Los valores constantes de fechas u horas deben ser escritos entre apóstrofes, para que sean reconocidos como tales, o de lo contrario se tomarán como operaciones aritméticas.
Esto:

Código MySQL:

SELECT
  TSS.idsubserie,
  md,
  TSS.codigo,
  TSS.nombre,
  TS.codigo codigoserie,
  TS.nombre nombreserie,
  UA.codigo codigounidad,
  TPS.nombre tiposerie,
  valor_documental,
  retencion_agestion,
  retencion_acentral,
  disposicion_final,
  proc_disposicion_final,
  MIN(YEAR(fechae_inicial)) ainicial,
  MAX(YEAR(fechae_final)) afinal
FROM t_tabla_retencion_documental TRD
      INNER JOIN trd_subseries TSS
        ON TSS.idsubserie = TRD.idsubserie
      LEFT OUTER JOIN trd_series TS
        ON TS.idserie = TSS.idserie
        AND TS.idserie = TRD.idserie
      LEFT OUTER JOIN trd_unidades_administrativas UA
        ON UA.idunidadadm = TSS.idunidadadministrativa
      LEFT OUTER JOIN trd_tipos_serie TPS
        ON TS.idtiposerie = TPS.idtiposerie
WHERE
  UA.idunidadadm = 40
  AND TRD.fechae_inicial BETWEEN 2007-01-01 AND 2009-01-01
      GROUP BY TSS.idserie, TSS.idsubserie;
 

En realidad MySQL lo interpreta:

Código MySQL:

SELECT
  TSS.idsubserie,
  md,
  TSS.codigo,
  TSS.nombre,
  TS.codigo codigoserie,
  TS.nombre nombreserie,
  UA.codigo codigounidad,
  TPS.nombre tiposerie,
  valor_documental,
  retencion_agestion,
  retencion_acentral,
  disposicion_final,
  proc_disposicion_final,
  MIN(YEAR(fechae_inicial)) ainicial,
  MAX(YEAR(fechae_final)) afinal
FROM t_tabla_retencion_documental TRD
      INNER JOIN trd_subseries TSS
        ON TSS.idsubserie = TRD.idsubserie
      LEFT OUTER JOIN trd_series TS
        ON TS.idserie = TSS.idserie
        AND TS.idserie = TRD.idserie
      LEFT OUTER JOIN trd_unidades_administrativas UA
        ON UA.idunidadadm = TSS.idunidadadministrativa
      LEFT OUTER JOIN trd_tipos_serie TPS
        ON TS.idtiposerie = TPS.idtiposerie
WHERE
  UA.idunidadadm = 40
  AND TRD.fechae_inicial BETWEEN 2005 AND 2007
      GROUP BY TSS.idserie, TSS.idsubserie;
 

Debe ir:

Código MySQL:

SELECT
  TSS.idsubserie,
  md,
  TSS.codigo,
  TSS.nombre,
  TS.codigo codigoserie,
  TS.nombre nombreserie,
  UA.codigo codigounidad,
  TPS.nombre tiposerie,
  valor_documental,
  retencion_agestion,
  retencion_acentral,
  disposicion_final,
  proc_disposicion_final,
  MIN(YEAR(fechae_inicial)) ainicial,
  MAX(YEAR(fechae_final)) afinal
FROM t_tabla_retencion_documental TRD
      INNER JOIN trd_subseries TSS
        ON TSS.idsubserie = TRD.idsubserie
      LEFT OUTER JOIN trd_series TS
        ON TS.idserie = TSS.idserie
        AND TS.idserie = TRD.idserie
      LEFT OUTER JOIN trd_unidades_administrativas UA
        ON UA.idunidadadm = TSS.idunidadadministrativa
      LEFT OUTER JOIN trd_tipos_serie TPS
        ON TS.idtiposerie = TPS.idtiposerie
WHERE
  UA.idunidadadm = 40
  AND TRD.fechae_inicial BETWEEN '2007-01-01' AND '2009-01-01'
      GROUP BY TSS.idserie, TSS.idsubserie;
 

Además, nunca te olvides que el AND hace que las condiciones se deben cumplir al mismo tiempo, y si la fecha cumple la condición, pero el idunidadadm fuese 39, por ejemplo, el registro no saldrá porque no cumple con ambas.

Tip: Los AS son innecesarios desde hace años.
Tip 2: Usa alias en los nombres de tablas. Simplifica la lectura de las sentencias.
Tip 3: Trata de escribir estructurado, de esa forma se vuelve más sencillo entender la sentencia y se ven mejor los errores.