TRANSFORMAR DATA EM MÊS

SELECT
    CCCADCIR.reg,
    cccadcir.data,
    CCCADCIR.sala,
    tbprofis.nome AS MEDICO,

-- isto aqui é redundante e desnecessário e pode tornar a consulta lenta
-- (SELECT NOME FROM TBESPEC WHERE COD=CCLANCIR.ESP) ESP
-- a tabela TBESPEC já está informada no inner join, utilizando
-- inclusive o mesmo campo para relacionamento das tabelas
-- para obter o nome basta pegar o conteúdo de NOME da tabela já posicionada
    TBESPEC.NOME ESP

FROM
    CCCADCIR
INNER JOIN
    CCLANCIR ON CCCADCIR.ID=CCLANCIR.ID_CCCADCIR
INNER JOIN
    tbcbopro ON TBCBOPRO.cod=cclancir.cirur
INNER JOIN
    tbprofis ON TBPROFIS.ID=tbcbopro.id_tbprofis
LEFT join
    tbcremed on tbcremed.id_tbcbopro=tbcbopro.id
INNER JOIN
    TBESPEC ON TBESPEC.COD=CCLANCIR.ESP
WHERE
    cccadcir.hora_i between '2359' AND '0000' IS not null -- o que é este "IS not null"???
    AND CCCADCIR.DATA BETWEEN :data_inicio AND :data_final
    AND CCLANCIR.CDC = '11'
    AND CCLANCIR.ESP <> '0'

extract(month from CCCADICIR.DATA) mes -- firebird / oracle
month(CCCADICIR.DATA) mes -- sql server / mysql