0

I have to run a query on SQL Server 2014 using the node.js mssql package. To do so, use the query below with the two input parameters. When I execute the T-SQL code, the following error shows up:

RequestError: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

How can I solve it?

Input values:

  • IdCantiere: 14
  • Data: 2018-06-21 09:20:04.000

Node.js code:

async function CaricaRisorseCantiere(IdCantiere, Data) {

    var value = [];
    var query = "select  RisorseUmane.IdRisorseUmane,IdUtenteInserimento,u1.Nome+' '+u1.Cognome as InseritoDA,ExtraPreventivo,u2.Nome+' '+u2.Cognome as Risorsa,RisorseUmane.IdUtente,IdCantiere,CONVERT(VARCHAR(10), Data, 105) as Data,Descrizione,convert(varchar(5), OreInizio, 108) as OreInizio,convert(varchar(5), OreFine, 108) as OreFine,REPLACE(Pausa, '.', ':') as Pausa,convert(varchar(5), Cast(convert(varchar(5), (OreFine - OreInizio), 108) as datetime) - CAST(REPLACE(Pausa, '.', ':') as datetime), 108)  as TotaleOre   from RisorseUmane  inner join Utente as u1  on u1.IdUtente = RisorseUmane.IdUtenteInserimento   inner  join Utente as u2 on u2.IdUtente = RisorseUmane.IdUtente ";
    if (Data == "") {
        query = query + " where RisorseUmane.IdCantiere= @IdCantiere  order by convert(datetime, Data, 103) desc ";
    } else {
        query = query + " inner join RisorsaRapportoMobile on RisorsaRapportoMobile.IdRisorseUmane=RisorseUmane.IdRisorseUmane where RisorseUmane.IdCantiere= @IdCantiere and RisorsaRapportoMobile.IdRapportoMobile is null  and RisorseUmane.Data=convert(varchar,convert(datetime,@Data),105)  ";
    }
    const ret = await new Promise((resolve, reject) => {
        new sql.ConnectionPool(DbConfig.config).connect().then(pool => {
            if (Data == "") {
                return pool.request().input('IdCantiere', sql.Int, IdCantiere).query(query)
            } else {
                return pool.request().input('IdCantiere', sql.Int, IdCantiere).input('Data', sql.VarChar, Data).query(query)
            }
        }).then(result => {

            resolve(result);
            sql.close();

        }).catch(err => {

            console.log("Errore Risorse Model: ", err)
            reject(err);
            sql.close();
        })
    });

    for (var i = 0; i < ret.recordset.length; i++) {        
        value.push({            
            IdRisorseUmane: ret.recordset[i].IdRisorseUmane,
            IdUtenteInserimento: ret.recordset[i].IdUtenteInserimento,
            InseritoDA: ret.recordset[i].InseritoDA,
            ExtraPreventivo: ret.recordset[i].ExtraPreventivo,
            Risorsa: ret.recordset[i].Risorsa,
            Data: ret.recordset[i].Data,
            Descrizione: ret.recordset[i].Descrizione,
            TotaleOre: ret.recordset[i].TotaleOre    
        })
    }
    return value;
}

1 Answer 1

1

Instead of using the input call:

request.input('Data', sql.VarChar, Data)

The call should be changed to:

request.input('Data', sql.DateTime, new Date(Data));

Also, the SQL clause:

and RisorseUmane.Data=convert(varchar,convert(datetime,@Data),105)

Should become

and RisorseUmane.Data=convert(varchar,@Data,105)
Sign up to request clarification or add additional context in comments.

4 Comments

I had already tried your solution but it always gives me the same error I mentioned above
Thanks @riki, I'll test again!
your conversion generates me this 2018-06-21T07: 20: 04.000Z
Could you try changing the SQL: convert(varchar,convert(datetime,@Data),105) to simply convert(varchar,@Data,105)? Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.