MSSQL Datetime issues

OK, as you haven't replied, I cant test the DATABASE input and I've had to make several assumptions (i.e. there are likely some problems but you should be able to correct them)...

First, a screen shot demonstrating my earlier post...

This is the transform function - it is heavily commented - you should try to understand it

/*** transform payload object into a nice new object ***/


//A function to convert the DATE and TIME into a format the database likes
function formatDT(datepart, timepart){
    //split the dd/mm/yyy string by / into a 3 part array
    let dateparts = datepart.split('/');
    //now put the date parts (in reverse order) 
    //back into a string and add the time part 
    let newDateStr = `${dateparts[2]}/${dateparts[1]}/${dateparts[0]} ${timepart}`
    return newDateStr;
}

//get 
//store original in a msg property (for checking in debug output)
msg.origPayload = msg.payload;

//make a copy of the row data
let row = {...msg.payload};

//example data 11/12/2019;13:47:31;END;180302bt;0100/37707;2
//Giorno-       - 11/12/2019
//Ora           - 13:47:31
//Stato         - END
//Programma     - 180302bt
//Lotto         - 0100/37707
//Scheda        - 1

var lottoParts = row.Lotto.split("/");
msg.payload = {
  a: 9999, //not sure what this is about?
  DateTime: formatDT(row.Giorno, row.Ora), //Call a function to make the correct DATETIME string
  LottoRight5 : lottoParts[1],  //characters right of the '/' 
  LottoLeft4: lottoParts[0],  //characters left of the '/' 
  Stato: row.Stato, //add Stato to the payload
  Programma: row.Programma, //add Programma to the payload
  Scheda: row.Scheda //add Scheda to the payload
}

//Now we have an object with the clean values to write into DB
//return the msg with nicely formatted data, ready to insert straight into the query {{mustach}}
return msg;

Now the clean values in payload can be mapped straight into the SQL query...

INSERT INTO Output_Produzione (
  Centro_di_Lavoro, 
  data, 
  n_ord_prod,  
  n_operazione, 
  articolo, 
  VarINT1
) VALUES (
  payload.a, --not certain what this is supposed to be
  '{{payload.DateTime}}',
  '{{payload.LottoRight5}}',
  '{{payload.LottoLeft4}}',
  '{{payload.Programma}}',
  '{{payload.Scheda}}'
}

This is the flow...

[{"id":"87e2527.bb1f0b","type":"inject","z":"a96cce5.282c93","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":60,"wires":[["44a9df5d.d3ce3"]]},{"id":"e25bb35b.d6c6c","type":"csv","z":"a96cce5.282c93","name":"msg","sep":";","hdrin":"","hdrout":false,"multi":"one","ret":"\\n","temp":"Giorno, Ora, Stato, Programma, Lotto, Scheda","skip":"0","strings":true,"x":410,"y":120,"wires":[["9cd5b277.d0c9f","e14f2462.7363a8"]]},{"id":"44a9df5d.d3ce3","type":"file in","z":"a96cce5.282c93","name":"Quadra DVC EVO","filename":"\\\\10.0.0.4\\log_quadra\\1102_job_test.txt","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":290,"y":60,"wires":[["e25bb35b.d6c6c"]]},{"id":"39634da4.b9c5c2","type":"MSSQL","z":"a96cce5.282c93","mssqlCN":"47f92084.1a0af","name":"SQL_Insert","query":"INSERT INTO Output_Produzione (\n  Centro_di_Lavoro, \n  data, \n  n_ord_prod,  \n  n_operazione, \n  articolo, \n  VarINT1\n) VALUES (\n  payload.a,\n  '{{payload.DateTime}}',\n  '{{payload.LottoRight5}}',\n  '{{payload.LottoLeft4}}',\n  '{{payload.Programma}}',\n  '{{payload.Scheda}}'\n}","outField":"payload","returnType":"0","throwErrors":"0","x":610,"y":180,"wires":[["c0253a0d.0aba38"]]},{"id":"3e144157.4a543e","type":"catch","z":"a96cce5.282c93","name":"","scope":["39634da4.b9c5c2"],"uncaught":false,"x":570,"y":260,"wires":[["f85477fe.4e9cf8"]]},{"id":"f85477fe.4e9cf8","type":"debug","z":"a96cce5.282c93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":750,"y":260,"wires":[]},{"id":"c0253a0d.0aba38","type":"debug","z":"a96cce5.282c93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":750,"y":180,"wires":[]},{"id":"13de8553.8c230b","type":"inject","z":"a96cce5.282c93","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":110,"y":120,"wires":[["ea008fb6.193d"]]},{"id":"ea008fb6.193d","type":"template","z":"a96cce5.282c93","name":"fake data","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"02/12/2019;15:21:44;START;132300A;0030/23456;1\n02/12/2019;15:22:47;END;132300A;0030/23456;2\n02/12/2019;16:18:38;START;132400A;0030/23457;10\n02/12/2019;16:19:41;END;112400A;0030/23457;11\n02/12/2019;16:28:44;START;112400A;0030/23458;11\n02/12/2019;16:30:46;END;112400A;0030/23458;12\n02/12/2019;18:32:40;START;112800A;0030/23459;99\n02/12/2019;18:34:44;END;112800A;0030/23459;101\n03/12/2019;10:25:30;START;112800A;0030/23459;102\n03/12/2019;10:27:34;END;112800A;0030/23459;103\n03/12/2019;11:15:40;START;113900A;0030/23459;104\n03/12/2019;11:17:54;END;113900A;0030/23459;105\n03/12/2019;12:35:30;START;115600A;0030/25443;42\n03/12/2019;12:37:44;END;115600A;0030/25443;43\n04/12/2019;09:15:11;START;149800A;0030/25442;0\n04/12/2019;09:17:26;END;149800A;0030/25442;1\n04/12/2019;09:18:18;START;149800A;0030/25442;1\n04/12/2019;09:19:33;END;149800A;0030/25442;2\n04/12/2019;09:25:01;START;149800A;0030/25442;2\n04/12/2019;09:26:16;END;149800A;0030/25442;3\n05/12/2019;11:34:21;START;137500A;0030/25667;2\n05/12/2019;11:35:18;END;137500A;0030/25667;3\n05/12/2019;11:38:45;START;137500A;0030/25667;2\n05/12/2019;11:39:42;END;137500A;0030/25667;3\n11/12/2019;13:43:05;START;180302bt;0100/37707;0\n11/12/2019;13:44:31;END;180302bt;0100/37707;1\n11/12/2019;13:46:07;START;180302bt;0100/37707;1\n11/12/2019;13:47:31;END;180302bt;0100/37707;2\n11/12/2019;13:47:56;START;180302bt;0100/37707;2","output":"str","x":260,"y":120,"wires":[["e25bb35b.d6c6c","8feb88b5.17b418"]]},{"id":"8feb88b5.17b418","type":"debug","z":"a96cce5.282c93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":250,"y":180,"wires":[]},{"id":"9cd5b277.d0c9f","type":"debug","z":"a96cce5.282c93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":410,"y":180,"wires":[]},{"id":"e14f2462.7363a8","type":"function","z":"a96cce5.282c93","name":"transform data","func":"/*** transform payload object into a nice new object ***/\n\n\n//A function to convert the DATE and TIME into a format the database likes\nfunction formatDT(datepart, timepart){\n    //split the dd/mm/yyy string by / into a 3 part array\n    let dateparts = datepart.split('/');\n    //now put the date parts (in reverse order) \n    //back into a string and add the time part \n    let newDateStr = `${dateparts[2]}/${dateparts[1]}/${dateparts[0]} ${timepart}`\n    return newDateStr;\n}\n\n//get \n//store original in a msg property (for checking in debug output)\nmsg.origPayload = msg.payload;\n\n//make a copy of the row data\nlet row = {...msg.payload};\n\n//example data 11/12/2019;13:47:31;END;180302bt;0100/37707;2\n//Giorno-       - 11/12/2019\n//Ora           - 13:47:31\n//Stato         - END\n//Programma     - 180302bt\n//Lotto         - 0100/37707\n//Scheda        - 1\n\nvar lottoParts = row.Lotto.split(\"/\");\nmsg.payload = {\n  a: 9999, //not sure what this is about?\n  DateTime: formatDT(row.Giorno, row.Ora), //Call a function to make the correct DATETIME string\n  LottoRight5 : lottoParts[1],  //characters right of the '/' \n  LottoLeft4: lottoParts[0],  //characters left of the '/' \n  Stato: row.Stato, //add Stato to the payload\n  Programma: row.Programma, //add Programma to the payload\n  Scheda: row.Scheda //add Scheda to the payload\n}\n\n//Now we have an object with the clean values to write into DB\n//return the msg with nicely formatted data, ready to insert straight into the query {{mustach}}\nreturn msg;","outputs":1,"noerr":0,"x":580,"y":120,"wires":[["fcc4bfd8.b885c","39634da4.b9c5c2"]]},{"id":"fcc4bfd8.b885c","type":"debug","z":"a96cce5.282c93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":750,"y":120,"wires":[]},{"id":"47f92084.1a0af","type":"MSSQL-CN","z":"","name":"Scambio_NAV","server":"srv-dc","encyption":true,"database":"Scambio_NAV"}]

Sorry Steve, :hugs:

USE [Scambio_NAV]
GO

/****** Object: Table [dbo].[Output_Produzione] Script Date: 13/02/2020 07:03:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Output_Produzione](
[Centro_di_Lavoro] nchar NOT NULL CONSTRAINT [DF_Output_Produzione_Centro_di_Lavoro] DEFAULT ('n'),
[n_ord_prod] nchar NOT NULL CONSTRAINT [DF_Output_Produzione_n_ord_prod] DEFAULT (''),
[n_operazione] nvarchar NOT NULL CONSTRAINT [DF_Output_Produzione_n_operazione] DEFAULT (''),
[tempo] [int] NOT NULL CONSTRAINT [DF_Output_Produzione_tempo] DEFAULT ((0)),
[Tempo_setup] [int] NOT NULL CONSTRAINT [DF_Output_Produzione_Tempo_setup] DEFAULT ((0)),
[qta_output] [numeric](18, 0) NOT NULL CONSTRAINT [DF_Output_Produzione_qta_output] DEFAULT ((0)),
[qta_scarto] [numeric](18, 0) NOT NULL CONSTRAINT [DF_Output_Produzione_qta_scarto] DEFAULT ((0)),
[codice_scarto] nchar NOT NULL CONSTRAINT [DF_Output_Produzione_codice_scarto] DEFAULT (''),
[Articolo] nchar NOT NULL CONSTRAINT [DF_Output_Produzione_Articolo] DEFAULT (''),
[Validato] [bit] NOT NULL CONSTRAINT [DF_Output_Produzione_Validato] DEFAULT ((0)),
[id] [int] IDENTITY(1,1) NOT NULL,
[CodiceErrore] nchar NOT NULL CONSTRAINT [DF_Output_Produzione_CodiceErrore] DEFAULT ((0)),
[data] [datetime] NOT NULL CONSTRAINT [DF_Output_Produzione_data] DEFAULT (getdate()),
[VarINT1] [int] NOT NULL CONSTRAINT [DF_Output_Produzione_VarINT1] DEFAULT ((0)),
[VarINT2] [int] NOT NULL CONSTRAINT [DF_Output_Produzione_VarINT2] DEFAULT ((0)),
[VarCHAR1] nchar NOT NULL CONSTRAINT [DF_Output_Produzione_VarCHAR1] DEFAULT (''),
[VarCHAR2] nchar NOT NULL CONSTRAINT [DF_Output_Produzione_VarCHAR2] DEFAULT (''),
CONSTRAINT [PK_Output_Produzione] PRIMARY KEY CLUSTERED
(
[Centro_di_Lavoro] ASC,
[data] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

This is how to use the CSV File

The field Centro_di_Lavoro must be filled with 9999

Right now I didn't have the opportunity to test your last job,
This morning I'll not be in front of my PC, In the afternoon I'll be back.

Hi Steve, I'm out of the office but I can't hardly wait!
I tested but…
This is the query debug:

insert into Output_Produzione ( Centro_di_Lavoro,  data,  n_ord_prod,  n_operazione,  articolo,  VarINT1)VALUES (  9999,  '2020/01/25 15:21:45',  '23456',  '0030',  'AP11132399A',  '11')

x2F is the ASCII code of the / but I'm not sure if MSSQL can use ASCII code.


That is odd. can you screenshot the debug output of the msg attached to the output of transform data - show me what is being sent in to the SQL node and the matching debug output of the MSSQL node.

QUESTION: Was this error due to data from your file OR from the 'fake data'?
-- if this data is from the file in, try changing the encoding on the file in node named 'Quadra DVC EVO' to default or ASCII or something else - see if the / still gets changed to unicode value

The error is the same with both input (Fake data and real CSV File).
Tried to change encoding between default, ucs8 and ASCII but the result is the same.
This is the output from the function (CSV File):

{"a":9999,"DateTime":"2020/01/25 15:21:45","LottoRight5":"23456","LottoLeft4":"0030","Stato":"START","Programma":"AP11132399A","Scheda":11}

This is from SQL

insert into Output_Produzione ( Centro_di_Lavoro,  data,  n_ord_prod,  n_operazione,  articolo,  VarINT1)VALUES (  9999,  '2020/01/25 15:21:45',  '23456',  '0030',  'AP11132399A',  '11').

I'm using backticks because &#x2F in my typing area becomes / in the message.

Ah - silly me.

The mustache renderer treats {{var}} differently to {{{var}}}

Change all the mustache items in your SQL code to use triple curly brackets...
e.g.change {{payload.DateTime}} to {{{payload.DateTime}}}

Proof...

1 Like

You do need to do some reading on datetime formats and the differences between formats on different platforms. This is key to understanding how to work with the data.

https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15

In addition, it would be worth understanding what version of SQL Server you are using because I note that you are using format datetime but I think the current recommended format is datetime2.

Hi Steve! It Works! Many many thanks, you solve my headache and charge my spring of curiosity for NodeRed.
My opinion is that NodeRed is very powerful and the community is as powerful… And Steve-Mcl is a Jump ahead.
I'll never thank you enough.

Hi, Many thanks for this link! I didn't know about so many datetime formats.
I'm using SQL Server 2014.
I have tried to convert my data in the same format used by @Steve-Mcl using:
convert(varchar,'{{{payload.Giorno}}} {{{payload.Ora}}}',111)
But do not convert my data. No changes in the output format. I selected 111 because this is the format used By @Steve-Mcl and that with his function in the middle, works.
Tried with other format (0,1,2,9,20,101,112...)...just to test… but no conversions are made.
What I understood is that MSSQL accepts YYYY/MM/DD hh:mm:ss.

Hi Steve, sorry if I bother you again, the previous flow is closed but I have another issue and YOU are my Guru
:slight_smile:
This flow is very close to the previous.

The input is like this:

1201 3 12 2019 9 20 52 10590300 1 7 15 141

1201 3 12 2019 9 24 27 10590300 1 9 15 201

1201 3 12 2019 9 36 51 10590300 1 241 15 730

Please, look at this function:

//This Input is formatted with constant lenght
//with standard split(' '), multiple spaces returne multiple fields

var str = msg.payload;

var cdl = str.substr(0,4);
var D = str.substr(12,4)+'/'+str.substr(9,2)+'/'+str.substr(6,2)+' '+str.substr(17,2)+':'+str.substr(20,2)+'/'+str.substr(23,2);

//var dd = str.substr(6,2);
//var mm = str.substr(9,2);
//var yy = str.substr(12,4);
//var hh = str.substr(17,2);
//var m = str.substr(20,2);
//var ss = str.substr(23,2);

var Art = str.substr(27,8);
var P = str.substr(37,2);
var T = str.substr(40,3);
var R = str.substr(44,3);
var ET = str.substr(51,5);

msg.payload = {
Centro_di_lavoro : cdl,
Data: D,
Articolo :Art,
P20 : P,
Tool : T,
Ripetizioni : R,
Tempo : ET
}

//Now we have an object with the clean values to write into DB

return msg;

The function returns me this error:

"TypeError: str.substr is not a function"

Hi again. Could you edit your post and wrap your code parts in three backticks

```
like this
```
(It's difficult reading the code without proper formatting)

At a quick glance, I'd say your be better off with var parts = str.split(' '); then assemble the object using parts[0] , parts[1] etc.

Search the net for "js string split" to see examples

Hi Steve Thank you again for your precious time!
I can't use split (' ') because the log uses data with constant lenght that means thay I have multiple spaces between fields.
I tested split(' ') but multiple spaces return multiple fields and this is an issue.
So I'm trying with substr.
This is the code:

//This Input is formatted with constant lenght
//with standard split(' '), multiple spaces returne multiple fields

var str = msg.payload;

var cdl = str.substr(0,4);
var D = str.substr(12,4)+'/'+str.substr(9,2)+'/'+str.substr(6,2)+' '+str.substr(17,2)+':'+str.substr(20,2)+'/'+str.substr(23,2);

//var dd = str.substr(6,2);
//var mm = str.substr(9,2);
//var yy = str.substr(12,4);
//var hh = str.substr(17,2);
//var m = str.substr(20,2);
//var ss = str.substr(23,2);

var Art = str.substr(27,8);
var P = str.substr(37,2);
var T = str.substr(40,3);
var R = str.substr(44,3);
var ET = str.substr(51,5);

msg.payload = {
Centro_di_lavoro : cdl,
Data: D,
Articolo :Art,
P20 : P,
Tool : T,
Ripetizioni : R,
Tempo : ET
}

//Now we have an object with the clean values to write into DB

return msg;

Can't you just ignore the empty array parts e.g...

msg.payload = {
...
...
...
Ripetizioni : parts[19],
Tempo : parts[21]//skip [20] cos it's emoty
}


It's not so simple because spaces are avariable, depends of the n° of characters of the payload. i.e. 20 has one more space before than 120. This means that the number of parts changes… and even if we found a workaround… it's not so elegant :sunglasses:
Why the substr doesn't work?

We'll, as the error says "TypeError: str.substr is not a function" it could be msg.payload is not actually a string.

Put a debug node before the function, see what is in msg.payload before it enters the function.

Alternatively, add a node.warn in the function....

var str = msg.payload;
node.warn(str); // << add this 
var cdl = str.substr(0,4);

You should see a reddish entry in debug sidebar.

This is the debug output before the function:

msg : Object

object

_msgid: "945a22f6.2bd78"

topic: ""

payload: object

Tutto: "1201 3 12 2019 9 58 24 10754000 1 8 12 2"

filename: "\10.0.0.4\Log_Quadra\TempiMES_1201.txt"

parts: object

id: "945a22f6.2bd78"

index: 5

count: 7

and this is with node.warn(str);

So as you see from the debug output, msg.payload is an object with a property string named Tutto

So instead of

var str = msg.payload; 
var cdl = str.substr(0,4);

Do this...

var str = msg.payload.Tutto; //<< add .Tutto
var cdl = str.substr(0,4);

As I said in my very first post....

If you do, you will realise why I did that.

PS...
Strings have .substr() function, objects do not - that's why you're getting the error.

As Always, you are right and it works!
Thank you very much again.

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