# How to Insert Datetime to SQL DB using MSSQL node

**URL:** <https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203>\
**Category:** General\
**Tags:** database\
**Created:** [21 June 2022 10:35 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203 "2022-06-21T10:35:33Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![noob69](https://avatars.discourse-cdn.com/v4/letter/n/0ea827/32.png) [@noob69](https://discourse.nodered.org/u/noob69)\
**Post date:** [21 June 2022 10:35 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/1 "2022-06-21T10:35:33Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/4/64f7cbd73645c88237e9b62a862a3eb98e8d3ea5.png)

I've tried convert using parameter method.

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

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [21 June 2022 11:01 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/2 "2022-06-21T11:01:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![noob69](https://avatars.discourse-cdn.com/v4/letter/n/0ea827/32.png) [@noob69](https://discourse.nodered.org/u/noob69)\
**Post date:** [21 June 2022 11:35 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/3 "2022-06-21T11:35:00Z")

</div>

Hi, Here is how I done that

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/1/11fbf1c27840a66ea2eab307e221af363f743fc2.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [21 June 2022 11:47 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/4 "2022-06-21T11:47:52Z")

</div>

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

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

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

```

what do you see in the debug output?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [21 June 2022 12:17 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/6 "2022-06-21T12:17:17Z")

</div>

try in that function before MSSQL

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

```

to force it to a date type

---

<div class="post-metadata">

**Author:** ![noob69](https://avatars.discourse-cdn.com/v4/letter/n/0ea827/32.png) [@noob69](https://discourse.nodered.org/u/noob69)\
**Post date:** [21 June 2022 12:37 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/7 "2022-06-21T12:37:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [21 June 2022 12:39 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/8 "2022-06-21T12:39:33Z")

</div>

> [@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(...)`

---

<div class="post-metadata">

**Author:** ![noob69](https://avatars.discourse-cdn.com/v4/letter/n/0ea827/32.png) [@noob69](https://discourse.nodered.org/u/noob69)\
**Post date:** [21 June 2022 15:49 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/9 "2022-06-21T15:49:38Z")

</div>

Yes I've done that

---

<div class="post-metadata">

**Author:** ![noob69](https://avatars.discourse-cdn.com/v4/letter/n/0ea827/32.png) [@noob69](https://discourse.nodered.org/u/noob69)\
**Post date:** [22 June 2022 03:52 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/10 "2022-06-22T03:52:22Z")

</div>

> [@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"]`

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [22 June 2022 06:38 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/11 "2022-06-22T06:38:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![noob69](https://avatars.discourse-cdn.com/v4/letter/n/0ea827/32.png) [@noob69](https://discourse.nodered.org/u/noob69)\
**Post date:** [22 June 2022 07:01 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/12 "2022-06-22T07:01:31Z")

</div>

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

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [22 June 2022 07:04 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/13 "2022-06-22T07:04:36Z")

</div>

> [@noob69](#):
>
> `[expiry] [datetime] NULL,`

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

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/f/bf42b1acfef710d49254c55e2cb442d0c6f19bab.png)

Also, looking closer at your query...

> [@noob69](#):
>
> ```auto
> 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...

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

```

but you send

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

```

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [22 June 2022 07:13 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/14 "2022-06-22T07:13:53Z")

</div>

My suspicion is ...

### 1. change your query to

```auto
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)

---

<div class="post-metadata">

**Author:** ![noob69](https://avatars.discourse-cdn.com/v4/letter/n/0ea827/32.png) [@noob69](https://discourse.nodered.org/u/noob69)\
**Post date:** [22 June 2022 07:17 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/15 "2022-06-22T07:17:35Z")

</div>

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 .**

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [6 July 2022 07:18 UTC](https://discourse.nodered.org/t/how-to-insert-datetime-to-sql-db-using-mssql-node/64203/16 "2022-07-06T07:18:01Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.