Tedious SQL Server TVP : parameter.value.getTime is not a function for datetime

Question

I am trying to use TVP with tedious and keep getting this error while using a DateTime parameter.

The exception while building the request is :

 days = Math.floor((parameter.value.getTime() - UTC_EPOCH_DATE.getTime()) /
 (1000 * 60 * 60 * 24));
                                             ^

Exception:

TypeError: parameter.value.getTime is not a function

The code looks like this

/*declare table*/``    
let table = {
        columns: [
            { name: 'a', type: TYPES.VarChar, length: 50, nullable: true },
            { name: 'b', type: TYPES.Int},
            { name: 'c', type: TYPES.DateTime}

        ],
        rows: [
           ['hello tvp', 777,'05/08/07 12:35 PM'],
           ['OLO', 45,'05/08/16 1:30 AM']
        ]
    };

/*request code*/
var request = new Request("MyCustomStoredProcedure", function (err, rowCount) {
                    if (!err) {
                        callback(err)
                        logger.info("rowCount", rowCount)
                    } else {
                        callback(rowCount)
                        logger.error("Error => ", err)
                    }
                });
                request.addParameter('tvp', TYPES.TVP, table);

                request.on('row', function (columns) {
                     logger.info("data", columns)
                });

                connection.callProcedure(request);



CREATE TYPE TestType AS TABLE (a VARCHAR(50), b INT, c DateTime);

CREATE PROCEDURE MyCustomStoredProcedure 
    (@tvp TestType readonly) 
AS 
    SELECT * 
    FROM @tvp

Looking at the tedious code for data-types.js I found that the parameter.value was a string and not an object .

Not sure what am I doing wrong here.

What I tried

without datetime - works
with DateTime2 - the incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 (""): Data type 0x03 is unknown.
With https://github.com/patriksimek/node-mssql, but internally it again uses Tedious

1. So only the first bit fails? I don't see the first lot of code in the second bit so what are we looking at? 2. Use ISO-8601 date time formats: `'05/08/16 1:30 AM'` is not ISO-8601 and is at best ambiguous, at worst it causes an error — gbn, May 26 '16 at 06:24

score 5 · Answer 1 · answered Jun 27 '16 at 21:23

5

I used Varchar, instead of DateTime, and it solved my problem. I understand it is not the best solution, but in the interest of time, I took it.

answered Jun 27 '16 at 21:23

Rajat banerjee

1,781
3
17
35

score 1 · Answer 2 · edited Feb 16 '17 at 10:37

1

your date format has issues. javascript's datetime has a timezone element SQL's datetime does not have timezone.

Try

['hello tvp', 777,new Date('05/08/07 12:35 PM')]

I encountered the same issue adding in the timezone resolves it.

edited Feb 16 '17 at 10:37

Ahmed Ashour

5,179
10
35
56

answered Feb 16 '17 at 10:18

phiroz

11
1

score 1 · Answer 3 · edited Mar 20 '18 at 00:46

Here is my workaround; which is working for me.

By the way, I've confirmed that this bad behaviour is a known bug in "tediousjs" and/or "mssql", still unfixed as of Nov 2017.

https://github.com/tediousjs/tedious/pull/623

I am grateful to Rajat Banerjee and Ahmed Ashour; without their answers, above, I would be in deep trouble. The error message is so terribly misleading.

Rajat says "use VarChar" and Ahmed says "no timezone". So here is my workaround, broken down into minute detail.

Start by getting an value from which to create a date. Note that you want UTC, since that is what SQL Server expects.

 var myDateInt = Date.UTC(2018, 12, 31, 0, 0, 0)
 console.log(myDateInt)
 // 1548892800000

 var myDateObj = new Date(myDateInt)
 console.log(myDateObj)
 // Wed Jan 30 2019 18:00:00 GMT-0600 (CST)

The work around is three steps.
Step 1, get an ISO string.

 var myDateISOString = myDateObj.toISOString()
 console.log(myDateISOString)
 // 2019-01-31T00:00:00.000Z

In the ISO string, the time-zone is always "zulu", which is expressed by the letter 'Z' at the end.
Step 2 is remove the time-zone.

 var myDateSQLString = myDateISOString.replace(/Z$/,"")
 console.log(myDateSQLString)
 // 2019-01-31T00:00:00.000

Step 3 is use 'VarChar()' when declaring the value to the MsSql object.

 mssql_request.input(myDateSQLString,'VarChar(30)',value);

That worked for me.

Tedious SQL Server TVP : parameter.value.getTime is not a function for datetime

3 Answers3