# Declaring for datatime value

**URL:** <https://discourse.nodered.org/t/declaring-for-datatime-value/54207>\
**Category:** General\
**Created:** [24 November 2021 11:46 UTC](https://discourse.nodered.org/t/declaring-for-datatime-value/54207 "2021-11-24T11:46:20Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![TROJAN053](https://avatars.discourse-cdn.com/v4/letter/t/7cd45c/32.png) [@TROJAN053](https://discourse.nodered.org/u/TROJAN053)\
**Post date:** [24 November 2021 11:46 UTC](https://discourse.nodered.org/t/declaring-for-datatime-value/54207/1 "2021-11-24T11:46:21Z")

</div>

```auto
[{"id":"af47db90.c714c8","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"252febcc.2b8d54","type":"mqtt in","z":"af47db90.c714c8","name":"","topic":"VEHICLE/LINE3/STN_DATA","qos":"2","datatype":"json","broker":"9bd25e5c.ec8f5","nl":false,"rap":true,"rh":0,"x":120,"y":160,"wires":[["adfde7e4.eb8648"]]},{"id":"adfde7e4.eb8648","type":"function","z":"af47db90.c714c8","name":"","func":"var a=msg.payload;\n//var plcread = a;\n//var t = msg.topic;\n//var topic = t.split(\",\");\nvar b = a[0].Station_No;\nvar c = a[1].Conveyor_Name;\nvar d = a[2].Andon_PC;\nvar e = a[3].Start_Time;\nvar f = a[4].Stop_Time;\nvar g = a[5].Duration;\nvar h = a[6].Area_Name;\n//var h = selobj.Date_Time;\n\n//var dt = new Date(h);\n//var dts = dt.getFullYear().toString()+\"-\"+(dt.getMonth()+1).toString() + \"-\" + dt.getDate().toString() +\" \" + dt.getHours().toString()+\":\"+ dt.getMinutes().toString()+\":\"+ dt.getSeconds().toString()+\".\"+ dt.getMilliseconds().toString();\n\n//var P0 = dts;\n\n//msg.payload =\"'\"+b+\"'\"+','+\"'\"c+\"'\"+','+d+','+e+','+f+','+\"'\"+g+\"'\";\nmsg.payload =\"'\"+b+\"'\"+','+ \"'\"+c+\"'\"+','+d+','+e+','+f+','+g+','+\"'\"+h+\"'\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":160,"wires":[["ca96ed2b.fdc13"]]},{"id":"57a5e27a.f34b5c","type":"mqtt in","z":"af47db90.c714c8","name":"","topic":"VEHICLE/LINE1/STN_DATA","qos":"2","datatype":"json","broker":"9bd25e5c.ec8f5","nl":false,"rap":true,"rh":0,"x":120,"y":220,"wires":[["522ffe3.f9aa1"]]},{"id":"ca96ed2b.fdc13","type":"debug","z":"af47db90.c714c8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":710,"y":120,"wires":[]},{"id":"522ffe3.f9aa1","type":"function","z":"af47db90.c714c8","name":"","func":"var a=msg.payload;\n//var plcread = a;\n//var t = msg.topic;\n//var topic = t.split(\",\");\nvar d = msg.myrawdate;\nvar dt = new Date(d);\nvar dts = dt.getFullYear().toString()+\"-\"+(dt.getMonth()+1).toString() + \"-\" + dt.getDate().toString() +\" \" + dt.getHours().toString()+\":\"+ dt.getMinutes().toString()+\":\"+ dt.getSeconds().toString()+\".\"+ dt.getMilliseconds().toString();\n\n\nvar Z = dts;\nvar b = a[0].Station_No;\nvar c = a[1].Conveyor_Name;\nvar e = a[2].Andon_PC;\nvar f = a[3].Start_Time;\nvar g = a[4].Stop_Time;\nvar h = a[5].Duration;\nvar i = a[6].Area_Name;\n\n//msg.payload =\"'\"+b+\"'\"+','+\"'\"c+\"'\"+','+d+','+e+','+f+','+\"'\"+g+\"'\";\nmsg.payload =\"'\"+b+\"'\"+','+ \"'\"+c+\"'\"+','+e+','+f+','+g+','+h+','+\"'\"+i+\"'\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":220,"wires":[["c833c214.b3e7b"]]},{"id":"2d05fa48.d062f6","type":"debug","z":"af47db90.c714c8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":730,"y":220,"wires":[]},{"id":"c833c214.b3e7b","type":"MSSQL","z":"af47db90.c714c8","mssqlCN":"3ffb8ccd.92c0d4","name":"Vehicle Andon","outField":"payload","returnType":0,"throwErrors":"0","query":"DECLARE @Station varchar(50)\nDECLARE @Conveyor varchar(30)\nDECLARE @Pc varchar(10)\nDECLARE @Start datetime\nDECLARE @Stop datetime\nDECLARE @Duration float\nDECLARE @Line varchar(50)\n\nSET @Station = '{{{payload.0}}}';\nSET @Conveyor = '{{{payload.1}}}';\nSET @Pc = '{{{payload.2}}}';\nSET @Start = '{{{payload.3}}}';\nSET @Stop = '{{{payload.4}}}';\nSET @Duration = '{{{payload.5}}}';\nSET @Line = '{{{payload.6}}}';\nINSERT INTO dbo.Vehicle_Andon\n (Station, Conveyor, Andon_PC, Start_Time, Close_Time, Duration, LINE_NAME )\nVALUES ( @Station,\n @Conveyor,\n @Pc,\n @Start,\n @Stop,\n @Duration,\n @Line \n )","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"rows","rowsType":"msg","params":[],"x":520,"y":220,"wires":[["2d05fa48.d062f6"]]},{"id":"9bd25e5c.ec8f5","type":"mqtt-broker","name":"HiveMQ","broker":"172.27.138.122","port":"1883","clientid":"","usetls":false,"protocolVersion":"4","keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"sessionExpiry":""},{"id":"3ffb8ccd.92c0d4","type":"MSSQL-CN","tdsVersion":"7_4","name":"Andon_data_central_server","server":"172.27.142.80","port":"1433","encyption":true,"trustServerCertificate":true,"database":"Vehicle_Factory_Andon","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]

```

To insert data to MSSQL i am using this flow. Where startime and stoptime is have datetime type data in SQL server. An error is throwing by sql which i have attached.

 ![error](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/1/31af6c99156b1663c0af4de06124c15ccb5585a3.jpeg)

---

<div class="post-metadata">

**Author:** ![TROJAN053](https://avatars.discourse-cdn.com/v4/letter/t/7cd45c/32.png) [@TROJAN053](https://discourse.nodered.org/u/TROJAN053)\
**Post date:** [24 November 2021 11:47 UTC](https://discourse.nodered.org/t/declaring-for-datatime-value/54207/2 "2021-11-24T11:47:07Z")

</div>

My data string is  
''STATION 4'',''Conveyor 1'','I',2021-11-24 17:8:55.684,2021-11-24 17:12:31.721,216,'CHASSIS\_ASSEMBLY\_LINE1'

---

<div class="post-metadata">

**Author:** ![shrickus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/shrickus/32/517_2.png) [@shrickus](https://discourse.nodered.org/u/shrickus)\
**Post date:** [26 November 2021 16:47 UTC](https://discourse.nodered.org/t/declaring-for-datatime-value/54207/3 "2021-11-26T16:47:39Z")

</div>

> [@TROJAN053](#):
>
> 2021-11-24 17:8:55.684

That sample datetime value looks odd, without a leading '0' on the minutes... what timezone is that string supposed to represent, local time or UTC?

The `new Date(...)` Javascript will interpret that string as local time, since there is no timezone information inside the string -- so in my timezone (US/Eastern), it returns this UTC timestamp:

```auto
> new Date('2021-11-24 17:8:55.684')
2021-11-24T22:08:55.684Z

```

which is directly usable inside your sql statement. So I think you can eliminate all of the effort of rebuilding the dts string (which seems to return exactly what you put in?)

```auto
> dt.getFullYear().toString() + "-" + (dt.getMonth()+1).toString() + "-" + dt.getDate().toString() +" " + dt.getHours().toString() + ":" + dt.getMinutes().toString() + ":" + dt.getSeconds().toString() + "." + dt.getMilliseconds().toString();
'2021-11-24 17:8:55.684'

```

Incidentally, you do not need to add the `.toString()` on each var since that is what happens by default when doing string concatenation.

---

<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:** [25 January 2022 16:47 UTC](https://discourse.nodered.org/t/declaring-for-datatime-value/54207/4 "2022-01-25T16:47:45Z")

</div>

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