How to Insert Datetime to SQL DB using MSSQL node

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: -

image

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);

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

1 Like

Hi, Here is how I done that

image

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?

1 Like

try in that function before MSSQL

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

to force it to a date type

1 Like

Still same , 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(...)

Yes I've done that

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

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

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

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

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

image

Also, looking closer at your query...

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

1 Like

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)

1 Like

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 .