How to Insert Datetime to SQL DB using MSSQL node

[noob69]

Hi, I'm trying to insert following date portion in to SQL db but it showing me this error."Conversion failed when converting date and/or time from character string." .

My msg: -

I've tried convert using parameter method.

USE [data]
INSERT INTO [dbo].[tags]
          ([id_tag]
           ,[name]
           ,[expiry]
           ,[group_m]
           ,[In_transaction]
           ,[blocked])
VALUES (@id,@name,@group_m,(convert(datetime,@dates,5)),@in_trans,@blocked);

[Steve-Mcl]

show me how you have setup the MSSQL-PLUS node and parameters

[noob69]

Hi, Here is how I done that

[Steve-Mcl]

ok, I suspect your date is not a date but a string.

Add a function node before the MSSQL node and enter...

node.warn(["typeof msg.date", typeof msg.date])
return msg;

what do you see in the debug output?

[Steve-Mcl]

try in that function before MSSQL

msg.date = new Date(msg.date)
return msg;

to force it to a date type

[noob69]

Still same , I thought SQL convert query will do the trick

[Steve-Mcl]

@noob69

I thought SQL convert query will do the trick

No, that requires the "string" be in a certain format (different to JS ISO format)

• did you add the function before the MSSQL to change the date string to a data object?
• did you remove the SQL convert and pass @dates directly in to the values(...)

[noob69]

Yes I've done that

[noob69]

@Steve-Mcl

node.warn(["typeof msg.date", typeof msg.date])

Hi, I've checked this after the modification that you have suggested. The output is like this.

["typeof msg.date","object"]

[Steve-Mcl]

Could you share a CREATE TABLE script and export a demo flow - I will take a look.

[noob69]

Here is the tabele. Note that Id column is going to modify to generate auto id with a seed

CREATE TABLE [dbo].[tags](
	[id] [varchar](50) NOT NULL,
	[id_tag] [varchar](50) NOT NULL,
	[name] [varchar](50) NULL,
	[expiry] [datetime] NULL,
	[group_m] [varchar](50) NULL,
	[In_transaction] [bit] NOT NULL,
	[blocked] [bit] NOT NULL
) ON [PRIMARY]
GO

[Steve-Mcl]

@noob69

[expiry] [datetime] NULL,

Set your MSSQL node parameter to datetime to match your schema and try again.

Also, looking closer at your query...

@noob69

USE [data]
INSERT INTO [dbo].[tags]
          ([id_tag]
           ,[name]
           ,[expiry]
           ,[group_m]
           ,[In_transaction]
           ,[blocked])
VALUES (@id,@name,@group_m,(convert(datetime,@dates,5)),@in_trans,@blocked);

If ID is an auto increment, DO NOT SEND a value for that field
ALSO - your fields are written in this order...

([id_tag]   --1
,[name]     --2
,[expiry]   --3
,[group_m]  --4

but you send

@id,           --1
@name,         --2
@group_m,      --3
(convert(datetime,@dates,5)) --4

In other words, you are sending @group_m to the [expiry] field

[Steve-Mcl]

My suspicion is ...

1. change your query to

USE [data]
INSERT INTO [dbo].[tags]
       ( [id_tag]
        ,[name]
        ,[expiry]
        ,[group_m]
        ,[In_transaction]
        ,[blocked] )
VALUES ( @id, 
         @name, 
         @dates, 
         @group_m, 
         @in_trans, 
         @blocked );

2. change the @dates parameter to datetime type

... it will just work

(remember to delete [id_tag] and @id if id_tag is an auto increment field)

[noob69]

Yeah, Huge thanks for the noticing it. I was trying to update with datetime.now with a parameters. Then It alos failed. Thank You Very Much @Steve-Mcl .