Node-mssql: sql.DateTime fails on SQL 2005

[adnan-kamili]

 days = Math.floor((parameter.value.getTime() - UTC_EPOCH_DATE.getTim
                                    ^
 TypeError: undefined is not a function

In data-type.js you have missed to convert date string to object as you have done for DateTime2

       if (parameter.value != null) {
      parameter.value = new Date(+parameter.value);  // this line is missing -  line 261
       if (options.useUTC) {

[agarstang]

Also getting this on version 4.0.4.

[atsage]

As stated above, the fix is simple. Please change the if statement on line 300 to

  if (parameter.value != null) {
    var days = void 0,
        dstDiff = void 0,
        milliseconds = void 0,
        seconds = void 0,
        threeHundredthsOfSecond = void 0,
        time = new Date(+parameter.value);
    if (options.useUTC) {
      days = Math.floor((time.getTime() - UTC_EPOCH_DATE.getTime()) / (1000 * 60 * 60 * 24));
      seconds = time.getUTCHours() * 60 * 60;
      seconds += time.getUTCMinutes() * 60;
      seconds += time.getUTCSeconds();
      milliseconds = seconds * 1000 + time.getUTCMilliseconds();
    } else {
      dstDiff = -(time.getTimezoneOffset() - EPOCH_DATE.getTimezoneOffset()) * 60 * 1000;
      days = Math.floor((time.getTime() - EPOCH_DATE.getTime() + dstDiff) / (1000 * 60 * 60 * 24));
      seconds = time.getHours() * 60 * 60;
      seconds += time.getMinutes() * 60;
      seconds += time.getSeconds();
      milliseconds = seconds * 1000 + time.getMilliseconds();
    }

    threeHundredthsOfSecond = milliseconds / (3 + 1 / 3);
    threeHundredthsOfSecond = Math.round(threeHundredthsOfSecond);

    buffer.writeUInt8(8);
    buffer.writeInt32LE(days);

    return buffer.writeUInt32LE(threeHundredthsOfSecond);
  }

[agarstang]

This looks like it needs to be on the tedious project.

Replicated this issue and linked.

[fontzter]

A hack workaround that I used, which doesn't require changing the source.

Using the npm dateformat package ...

.input('date_field', sql.VarChar, dateFormat(req.payload.js_date, 'yyyy-mm-dd HH:MM:ss'))

IOW, send in the date as a varchar formatted as MSSQL likes it.

Hth

[agarstang]

I'm surprised you find that solves this issue @fontzter as the issue is with the module not casting the string to a DateTime object (therefore the required methods are missing).

Are you sure it's this issue you are solving with that?

What version of the module are you using?

[fontzter]

Yes, @agarstang it works. What I meant is that you pass/use a VarChar in your query/proc instead of a DateTime, so it never needs to be converted. SQL does the conversion when it processes the query; and, as long as the varchar string holding the date is formatted in an acceptable way, the implicit conversion works fine. So, something like this works for me...

new sql.Request()
  .input('an_id', sql.Int, some_js_id)
  .input('a_js_date_formated_as_a_string', sql.VarChar, dateFormat(a_js_date_object, 'yyyy-mm-dd HH:MM:ss'))
  .query(`update some_table
            set an_actual_sql_DateTime_field = @a_js_date_formated_as_a_string
            where the_id = @an_id`
  ).then(...

I'm using 4.1.0

[dhensby]

I'm closing this as it's a tedious issue, but also I'd welcome any PRs to work around this problem