Aparte de recomendarte expresamente el uso de generados, tal y como se indica, ya que están al margen de las transacciones y por tanto te evitaran problemas que si no los utilizas los tendrás MAS QUE SEGURO, te quiero hacer dos comentarios a tu pregunta :
Te debes de asegurar que la consulta que obtienes esté ordenada por ese campo que quieres incrementar.
Cuando consultas entre fechas, evidentemente no tienes porqué obtener todos los registros existentes, y por lo tanto si vas a coger el último número + 1 no tiene porqué ser el número que buscas ya que ese número ya puede existir al haber consultado entre márgenes de fechas, y por tanto te saltará el error.
Consejo : Olvídate por completo de ese método y estudia el tema de los generadores.
