MSSQL Datetime issues

[Simone]

@Steve-Mcl

CREATE TABLE Output_Produzione ( column1 datatype, column2 datatype, ..

Sorry Steve,

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.

[Simone]

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.

[Steve-Mcl]

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

[Simone]

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.

[Steve-Mcl]

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

[TotallyInformation]

@Simone

To be honest I'm going ahead without understanding the real issue. Why I need to change the datetime format? Which is the datetime format used by MSSQL Node?

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.

datetime (Transact-SQL) - SQL Server

Date and Time Conversions Using SQL Server

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.

[Simone]

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.

[Simone]

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.

[Simone]

Hi Steve, sorry if I bother you again, the previous flow is closed but I have another issue and YOU are my Guru

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"

[Steve-Mcl]

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)

[Steve-Mcl]

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

[Simone]

@Simone

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

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;

[Steve-Mcl]

@Simone

I tested split(' ') but multiple spaces return multiple fields and this is an issue.

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
}

[Simone]

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
Why the substr doesn't work?

[Steve-Mcl]

@Simone

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.

[Simone]

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

[Steve-Mcl]

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

@Steve-Mcl

read this Working with messages it will be vital in understanding

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.

[Simone]

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