Insert date and time SQL SERVER

General
1

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
011168×190 10.9 KB

02

2

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

3

Strings in SQL Queries require quotes around them.

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

4

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

image

5

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?

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

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
image717×550 19.6 KB

I recommend watching this playlist: Node-RED Essentials. 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.

8

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.

9

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

10

It worked perfectly, thanks so much for the help.

11

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.

12

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

13

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