0

I am using nodemssql package for sql server azure

Field

time(7)

>   RecurrenceType.columns.add('StartTime', mssql.Time(7));
>   RecurrenceType.columns.add('EndTime', mssql.Time(7));

Values

Start time : '01:01'

End time   : '13:01'

stored in db as : 

StartTime           EndTime
00:00:00.0000000    00:00:00.0000000

Please suggest how can store the value in time(7) field with nodemssql package with the exact value

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 3 (""), row 1, column 4: Data type 0x29 has an invalid data length or metadata length

Furqan Misarwala
  • 1,743
  • 6
  • 26
  • 53

1 Answers1

0

Furqan,

So long as your table column is configured as TIME(7) you should be able to insert directly into the column with any time string that has an accuracy of up to and including 9:

DECLARE @TimeString varchar(50) = '09:47:11.455651236'
DECLARE @TimeFormat AS time(7) = @TimeString
PRINT @TimeString
PRINT @TimeFormat

This is obviously if the string provided is in "hh:mi:ss.mmmmmmmmm". If the string is provided in any format other than this or exceeds a precision of 9 then the insert/update will fail with:

Msg 241, Level 16, State 1, Conversion failed when converting date and/or time from character string.

You'll need to modify the time string accordingly if this happens to comply with the time field requirements.

LinkOps
  • 331
  • 3
  • 14
  • I have change time(7) to time(0) and passing value as '01:01:00' but getting error as The data for table-valued parameter "@RecurrenceType" doesn't conform to the table type of the parameter. SQL Server error is: 8037, state: 80 – Furqan Misarwala Jun 08 '18 at 04:05
  • Furqan, Just pass the time as a varchar or string and SQL will do the conversion to TIME in the background automagically – LinkOps Jun 08 '18 at 12:59
  • LinkOps, I will try, but I have question, why time type is not working as parameter ? – Furqan Misarwala Jun 08 '18 at 13:16
  • Furqan, From what I can see here https://www.npmjs.com/package/mssql#data-types the datatype should be specified as `sql.Time(7)` and not `mssql.Time(7)` – LinkOps Jun 08 '18 at 13:30
  • var mssql = require("mssql"); because I have given the name as mssql instead of const sql = require('mssql') – Furqan Misarwala Jun 08 '18 at 13:32
  • In that case, I have no idea why it's not working. I tend to do asp.net c# and just use Time(7) directly when needed. – LinkOps Jun 08 '18 at 13:36