Subtract in PM and AM format in Sql Server 2008

0

Thank you in advance.

I'm going around trying to subtract PM from AM

The difference of format AM to PM the result is correct; however when rest of PM to AM the result is not the expected one

The insertion in the BD I do it this way:

public function insertarTiempo($usuario,$depto,$turno,$super,$jefe,$gerente,$area,$costo,$cantidad,$motivo,$status,$fecha,$fechaInicial,$fechaFinal)
{   


$idinserted = 0;



$week=  "" . date("W", strtotime($fecha));

$sql = "INSERT into tblHorasExtra VALUES('".$usuario."','".$depto."','".$turno."','".$super."','".$jefe."','".$gerente."','".$area."',".$costo.",'".$cantidad."','".$motivo."','".$status."',CONVERT(DATETIME, '".$fecha." 00:00:00', 103),0,0,0,'".$week."',CONVERT(DATETIME, '".$fechaInicial." 00:00:00', 103),CONVERT(DATETIME, '".$fechaFinal." 00:00:00', 103));  SELECT SCOPE_IDENTITY()";


//echo $sql;


$stmt = sqlsrv_query($this->conn,$sql);

    if( $stmt === false) {

        die( print_r( sqlsrv_errors(),true));
        echo " <br/>" . $sql;   
        $done = false;

    }else
    {
        $done = true;       
    }

    //Liberar memoria   
sqlsrv_next_result($stmt); 

sqlsrv_fetch($stmt); 

$idinserted =  sqlsrv_get_field($stmt, 0); 

    sqlsrv_free_stmt($stmt);    


    return  $idinserted;

}


Y la consulta de esta manera:

    public function mostrarDetalles($folio)
{

    $res = array();
    $i = 0;


    $stmt = sqlsrv_query( $this->conn,"SELECT t.idTiempo as folio,c.nombre + ' ' + c.apellidos as Supervisor,d.nombre + ' ' + d.apellidos as jefe ,f.nombre + ' ' + f.apellidos as gerente,ar.Descripcion as area,dp.Descripcion as depto,t.costo,t.cantidad,t.motivo,convert(nvarchar(30), t.fecha, 103) as fecha ,convert(nvarchar(30), t.fechaInicial, 103) as fechaInicial,convert(nvarchar(30), t.fechaFinal, 103) as fechaFinal,t.status,t.AprobSuper,t.AprobJefe,t.AprobGerente,t.idSuper,t.idJefe,t.idGerente,tu.Descripcion as turno,t.semana from tblHorasExtra t JOIN tblpersonal c on t.idsuper = c.idclave JOIN tblpersonal d on t.idJefe = d.idclave JOIN tblpersonal f ON t.idgerente = f.idclave JOIN tblArea ar ON t.idArea = ar.idArea JOIN tblDepto dp ON dp.idDepto = t.idDepto JOIN tblturno tu ON t.idTurno = tu.idTurno where idTiempo = ".$folio." order by folio desc");
//select CONVERT(varchar(15),CAST('17:30:00.0000000' AS TIME),100)

    if( $stmt === false) {

        echo $qry;
        die( print_r( sqlsrv_errors(), true) );

    }

    while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {

        $res["folio"]  = $row['folio'];
        $res["Supervisor"]  = $row['Supervisor'];
        $res["jefe"]  = $row['jefe'];
        $res["Gerente"]  = $row['gerente'];
        $res["jefe"]  = $row['jefe'];
        $res["gerente"]  = $row['gerente'];
        $res["area"]  = $row['area'];
        $res["turno"]  = $row['turno'];
        $res["depto"]  = $row['depto'];
        $res["costo"]  = $row['costo'];
        $res["semana"]  = $row['semana'];
        $res["fechaInicial"]  = $row['fechaInicial'];
        $res["fechaFinal"]  = $row['fechaFinal'];
        $res["cantidad"]  = $row['cantidad'];
        $res["motivo"]  = $row['motivo'];
        $res["status"]  = $row['status'];

        $res["idSuper"]  = $row['idSuper']; 
        $res["idJefe"]  = $row['idJefe'];   
        $res["idGerente"]  = $row['idGerente']; 

        $res["fecha"]  = $row['fecha'];
        $res["AprobSuper"]  = $row['AprobSuper'];
        $res["AprobJefe"]  = $row['AprobJefe'];
        $res["AprobGerente"]  = $row['AprobGerente'];
    }

    sqlsrv_free_stmt($stmt);
    return $res;
}

And finally I do the subtraction to be able to show it in EXCEL

foreach ($empleados as $row) {

$datos = $db->consultarFecha($row['idemp'],$row['fecha']);


$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A' .$i, $row['idemp'])
        ->setCellValue('B'. $i, $row['Nombre'])
        ->setCellValue('C'. $i, $row['fecha'])
        ->setCellValue('D'. $i, $row['horas'])
        ->setCellValue('E'. $i, $row['area'])
        ->setCellValue('F'. $i, $detalles['depto'])
        ->setCellValue('G'. $i, $datos['fechaEntrada'])
        ->setCellValue('H'. $i, $datos['fechaSalida'])
        ->setCellValue('I'. $i, abs($datos['fechaEntrada']- $datos['fechaSalida'])); 

//Resta de Horas con valor absoluto, para no mostrar horas en negativo:  ->setCellValue('I'. $i, abs($datos['fechaEntrada']- $datos['fechaSalida'])); 
$i++;

}

    
asked by Noel L 22.06.2017 в 00:24
source

0 answers