# Insert date and time SQL SERVER

**URL:** <https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192>\
**Category:** General\
**Tags:** database\
**Created:** [14 December 2021 16:34 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192 "2021-12-14T16:34:19Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![mansanoj](https://avatars.discourse-cdn.com/v4/letter/m/5f8ce5/32.png) [@mansanoj](https://discourse.nodered.org/u/mansanoj)\
**Post date:** [14 December 2021 16:34 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/1 "2021-12-14T16:34:19Z")

</div>

I need to enter the current date and time via Node-RED into a SQL SERVER database, but it gives an error message.

I'm using the pallete: node-red-contrib-moment

I use the -3 hour time zone and then send it to a function. Inside the function, I do the insert for the bank and it shows an error, the code below the function follows.

var year = msg.payload.substring(0, 4)  
var month = msg.payload.substring(5, 7)  
var day = msg.payload.substring(8, 10)  
var hours = msg.payload.substring(11, 13)  
var minutes = msg.payload.substring(14, 16)  
var seconds = msg.payload.substring(17, 19)  
var date = + year + "-" + month + "-" + day + " " + hours + ":" + minutes + ":" + seconds  
msg.topic = "insert into Valores (TesteDataString) values (" + date + ")"  
return msg;

The database field is datetime, I also tried varchar, but it has the same error

 ![01](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/a/9a5c867dd9f90b8bc2c07b34ac85605130e0464a.png)  
 ![02](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/0/807478ab9cc9e8c52c88c1ca095a7c677339bb6a.png)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [14 December 2021 16:45 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/2 "2021-12-14T16:45:15Z")

</div>

I would think the value is a string so would require to be wrapped in quotes

> <https://stackoverflow.com/questions/63966976/how-to-insert-a-date-in-date-field-in-sql>

---

<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:** [14 December 2021 16:46 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/3 "2021-12-14T16:46:32Z")

</div>

Strings in SQL Queries require quotes around them.

To avoid this you should really use parameters (which also protect against SQL injection)

---

<div class="post-metadata">

**Author:** ![mickym2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mickym2/32/36128_2.png) [@mickym2](https://discourse.nodered.org/u/mickym2)\
**Post date:** [14 December 2021 16:49 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/4 "2021-12-14T16:49:25Z")

</div>

Why you need a function node - you can define the output format directly in the moment Node - even if this node is not necessary at all. 😉

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/8/880ae9ae2aa88501ad44ac17b3346b1d91b96bc6.png)

---

<div class="post-metadata">

**Author:** ![mansanoj](https://avatars.discourse-cdn.com/v4/letter/m/5f8ce5/32.png) [@mansanoj](https://discourse.nodered.org/u/mansanoj)\
**Post date:** [14 December 2021 17:06 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/5 "2021-12-14T17:06:07Z")

</div>

I appreciate the tips and apologize for the silly questions, but I'm a little new to using Node-RED and programming.

Where should I put these quotes? Could you help me showing me the code?

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [14 December 2021 17:14 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/6 "2021-12-14T17:14:34Z")

</div>

> [@mansanoj](#):
>
> msg.topic = "insert into Valores (TesteDataString) values (" + date + ")"

```auto
msg.topic = "insert into Valores (TesteDataString) values ('" + date + "')"

```

---

<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:** [14 December 2021 17:15 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/7 "2021-12-14T17:15:02Z")

</div>

> [@mansanoj](#):
>
> I'm a little new to using Node-RED

It isnt a node-red thing, its a SQL thing. You need single quotes around any string values you send.

e.g.

`msg.topic = "insert into Valores (TesteDataString) values ('" + date + "')"`

However - I still strongly recommend you use parameters

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/9/c9434606904b2f2851090d6fc7eea00709e904da.png)

* * *

> [@mansanoj](#):
>
> new to using Node-RED and programming.

I recommend watching this playlist: [Node-RED Essentials](https://www.youtube.com/playlist?list=PLyNBB9VCLmo1hyO-4fIZ08gqFcXBkHy-6). The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.

---

<div class="post-metadata">

**Author:** ![mansanoj](https://avatars.discourse-cdn.com/v4/letter/m/5f8ce5/32.png) [@mansanoj](https://discourse.nodered.org/u/mansanoj)\
**Post date:** [14 December 2021 17:49 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/8 "2021-12-14T17:49:22Z")

</div>

I've already performed some tests and I'm managing to insert the date and time into the database. Thank you very much for your help, I have been working on it for some time.

---

<div class="post-metadata">

**Author:** ![mansanoj](https://avatars.discourse-cdn.com/v4/letter/m/5f8ce5/32.png) [@mansanoj](https://discourse.nodered.org/u/mansanoj)\
**Post date:** [14 December 2021 17:50 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/9 "2021-12-14T17:50:19Z")

</div>

One doubt, when using the aforementioned parameter, the time is 3 hours ahead, how can I set the time zone?

---

<div class="post-metadata">

**Author:** ![mansanoj](https://avatars.discourse-cdn.com/v4/letter/m/5f8ce5/32.png) [@mansanoj](https://discourse.nodered.org/u/mansanoj)\
**Post date:** [14 December 2021 17:51 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/10 "2021-12-14T17:51:04Z")

</div>

It worked perfectly, thanks so much for the help.

---

<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:** [14 December 2021 18:08 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/11 "2021-12-14T18:08:19Z")

</div>

You don't/shouldn't. Times in a database should be saved as UTC otherwise you have all means of issues come DST

Do yourself a favour and store datetimes in UTC. You might not understand why right now but it will bite you in the long run.

---

<div class="post-metadata">

**Author:** ![mansanoj](https://avatars.discourse-cdn.com/v4/letter/m/5f8ce5/32.png) [@mansanoj](https://discourse.nodered.org/u/mansanoj)\
**Post date:** [14 December 2021 19:11 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/12 "2021-12-14T19:11:41Z")

</div>

I use Node-RED to collect data from a PLC, where I use Grafana to display this data. Saving the date and time in this UTC format also solved a problem I had with Grafana when displaying the date and time, thank you very much for your help

---

<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:** [28 December 2021 19:11 UTC](https://discourse.nodered.org/t/insert-date-and-time-sql-server/55192/13 "2021-12-28T19:11:48Z")

</div>

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