# MSSQL Datetime issues

**URL:** <https://discourse.nodered.org/t/mssql-datetime-issues/21540>\
**Category:** General\
**Created:** [11 February 2020 14:13 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540 "2020-02-11T14:13:52Z")\
**Posts on this page:** 19\
**Page:** 2

<div class="post-metadata">

**Author:** ![Simone](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Simone](https://discourse.nodered.org/u/Simone)\
**Post date:** [13 February 2020 06:15 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/21 "2020-02-13T06:15:38Z")

</div>

> [@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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/0/40004a5f0756ccbe86575c3933569cfa9e82b9be.png)

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.

---

<div class="post-metadata">

**Author:** ![Simone](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Simone](https://discourse.nodered.org/u/Simone)\
**Post date:** [13 February 2020 08:18 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/22 "2020-02-13T08:18:13Z")

</div>

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

```auto
insert into Output_Produzione ( Centro_di_Lavoro, data, n_ord_prod, n_operazione, articolo, VarINT1)VALUES ( 9999, '2020&#x2F;01&#x2F;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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/9/99f65b63d5255ac04adde8e451c1705daba344d2.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/a/ca5586f647c1d4b90a62823d38ecc8443abdb11d.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:** [13 February 2020 08:56 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/23 "2020-02-13T08:56:50Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Simone](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Simone](https://discourse.nodered.org/u/Simone)\
**Post date:** [13 February 2020 14:09 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/24 "2020-02-13T14:09:13Z")

</div>

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

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

```

This is from SQL

```auto
insert into Output_Produzione ( Centro_di_Lavoro, data, n_ord_prod, n_operazione, articolo, VarINT1)VALUES ( 9999, '2020&#x2F;01&#x2F;25 15:21:45', '23456', '0030', 'AP11132399A', '11').

```

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

---

<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:** [13 February 2020 16:05 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/25 "2020-02-13T16:05:26Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [13 February 2020 17:07 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/26 "2020-02-13T17:07:02Z")

</div>

> [@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](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15)**
>
> datetime (Transact-SQL)

> **[Date and Time Conversions Using SQL Server](https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/)**
>
> Learn SQL date format options with the SQL CONVERT function when working with date data types in 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`.

---

<div class="post-metadata">

**Author:** ![Simone](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Simone](https://discourse.nodered.org/u/Simone)\
**Post date:** [13 February 2020 21:43 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/27 "2020-02-13T21:43:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Simone](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Simone](https://discourse.nodered.org/u/Simone)\
**Post date:** [13 February 2020 22:15 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/28 "2020-02-13T22:15:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Simone](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Simone](https://discourse.nodered.org/u/Simone)\
**Post date:** [15 February 2020 07:54 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/29 "2020-02-15T07:54:31Z")

</div>

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"

---

<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:** [15 February 2020 19:01 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/30 "2020-02-15T19:01:14Z")

</div>

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)

---

<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:** [15 February 2020 19:23 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/31 "2020-02-15T19:23:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Simone](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Simone](https://discourse.nodered.org/u/Simone)\
**Post date:** [15 February 2020 19:41 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/32 "2020-02-15T19:41:19Z")

</div>

> [@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:

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

```

---

<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:** [15 February 2020 20:36 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/33 "2020-02-15T20:36:08Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![Simone](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Simone](https://discourse.nodered.org/u/Simone)\
**Post date:** [15 February 2020 22:51 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/34 "2020-02-15T22:51:13Z")

</div>

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?

---

<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:** [15 February 2020 23:03 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/35 "2020-02-15T23:03:05Z")

</div>

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

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

```

You should see a reddish entry in debug sidebar.

---

<div class="post-metadata">

**Author:** ![Simone](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Simone](https://discourse.nodered.org/u/Simone)\
**Post date:** [15 February 2020 23:17 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/36 "2020-02-15T23:17:21Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/1/71014ff4a73974f8b2d24cb2919a05b75f06f61f.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:** [16 February 2020 00:03 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/37 "2020-02-16T00:03:28Z")

</div>

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

So instead of

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

```

Do this...

```auto
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](https://nodered.org/docs/user-guide/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.

---

<div class="post-metadata">

**Author:** ![Simone](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Simone](https://discourse.nodered.org/u/Simone)\
**Post date:** [16 February 2020 13:40 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/38 "2020-02-16T13:40:37Z")

</div>

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

---

<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:** [1 March 2020 13:40 UTC](https://discourse.nodered.org/t/mssql-datetime-issues/21540/39 "2020-03-01T13:40:40Z")

</div>

Closed on 1 March 2020 13:40 UTC

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

[Previous page](https://discourse.nodered.org/t/mssql-datetime-issues/21540.md?page=1)