# Copy datetime from a table to another table

**URL:** https://discourse.nodered.org/t/copy-datetime-from-a-table-to-another-table/69354
**Category:** General
**Tags:** database
**Created:** [21 October 2022 04:04 UTC](https://discourse.nodered.org/t/copy-datetime-from-a-table-to-another-table/69354 "2022-10-21T04:04:04Z")
**Posts on this page:** 7
**Page:** 1

<div class="post-metadata">

### Author: ![cubical](https://avatars.discourse-cdn.com/v4/letter/c/f4b2a3/32.png) [@cubical](https://discourse.nodered.org/u/cubical)
#### Post date: [21 October 2022 04:04 UTC](https://discourse.nodered.org/t/copy-datetime-from-a-table-to-another-table/69354/1 "2022-10-21T04:04:04Z")

</div>

Hi guys, I've been trying to copy the datetime from a table to another table (mssql).

Issue: the datetime data type is being output as string.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/7/a781f39e4a5c85893d8b41429554700284fd689e.png)

Anyone knows how to counter the issue?

---

<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: [21 October 2022 07:48 UTC](https://discourse.nodered.org/t/copy-datetime-from-a-table-to-another-table/69354/2 "2022-10-21T07:48:02Z")

</div>

You will need to share the following info...

1. The flow part that generates the above
2. The name of the MSSQL node (node-red-contrib-????)
3. Example data (i.e. if there is something dynamic like a ModBus or HTTP API providing the date, we will not have this - you must provide sample data instead)

---

<div class="post-metadata">

### Author: ![cubical](https://avatars.discourse-cdn.com/v4/letter/c/f4b2a3/32.png) [@cubical](https://discourse.nodered.org/u/cubical)
#### Post date: [21 October 2022 08:10 UTC](https://discourse.nodered.org/t/copy-datetime-from-a-table-to-another-table/69354/3 "2022-10-21T08:10:26Z")

</div>

```auto
[
    {
        "id": "8cd81203fdd0b810",
        "type": "tab",
        "label": "Flow 3",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "db30356beffb06d1",
        "type": "inject",
        "z": "8cd81203fdd0b810",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 200,
        "y": 100,
        "wires": [
            [
                "275ac3bc4209aeed",
                "73c20ce674f86930"
            ]
        ]
    },
    {
        "id": "43b9ebe4429d0281",
        "type": "function",
        "z": "8cd81203fdd0b810",
        "name": "function 19",
        "func": "const NoOperator = msg.payload[\"W3.0\"];\nconst NoMaterial = msg.payload[\"W3.1\"];\nconst NoSchedule = msg.payload[\"W3.2\"];\nconst QualityIssue = msg.payload[\"W3.3\"];\nconst ScheduledMaintenance = msg.payload[\"W3.4\"];\nconst MachineBreakdown = msg.payload[\"W3.5\"];\nconst MaterialDrying = msg.payload[\"W3.6\"];\nconst MouldChange = msg.payload[\"W10.3\"];\nconst Trip = msg.payload[\"W9.1\"];\nconst TechBuyoff = msg.payload[\"W15.0\"];\nconst ProdRun = msg.payload[\"W15.2\"];\nconst TroubleShoot = msg.payload[\"W15.3\"];\nconst RunSample = msg.payload[\"W15.4\"];\n\nif (NoOperator == 1) {\n msg.payload = \"NO OPERATOR\";\n}\nelse if (NoMaterial == 1) {\n msg.payload = \"NO MATERIAL\";\n}\n\nelse if (NoSchedule == 1) {\n msg.payload = \"NO SCHEDULE\";\n}\nelse if (QualityIssue == 1) {\n msg.payload = \"QUALITY ISSUE\";\n}\nelse if (ScheduledMaintenance == 1) {\n msg.payload = \"SCHEDULED MAINTENANCE\";\n}\nelse if (MachineBreakdown == 1) {\n msg.payload = \"MACHINE BREAKDOWN\";\n}\nelse if (MaterialDrying == 1) {\n msg.payload = \"MATERIAL DRYING\";\n}\nelse if (MouldChange == 1) {\n msg.payload = \"MOULD CHANGE\";\n}\nelse if (Trip == 1) {\n msg.payload = \"MACHINE TRIP\";\n}\nelse if (TechBuyoff == 1) {\n msg.payload = \"TECHNICAL BUYOFF\";\n}\n\nelse if (TroubleShoot == 1) {\n msg.payload = \"TROUBLESHOOTING\";\n}\nelse if (RunSample == 1) {\n msg.payload = \"SAMPLE RUN\";\n}\nelse {\n msg.payload = 0;\n}\n\n\nreturn msg;\n\n//payload.category\n//payload.start.id_machine",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 570,
        "y": 140,
        "wires": [
            []
        ]
    },
    {
        "id": "73c20ce674f86930",
        "type": "change",
        "z": "8cd81203fdd0b810",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "No Operator",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 340,
        "y": 220,
        "wires": [
            [
                "cb1eef534c2fdcd0"
            ]
        ]
    },
    {
        "id": "275ac3bc4209aeed",
        "type": "MSSQL",
        "z": "8cd81203fdd0b810",
        "mssqlCN": "75a62ffff4c55db2",
        "name": "Start Time",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "query": "select * from trans_time where id_machine ='1';",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "editor",
        "paramsOpt": "queryParams",
        "paramsOptType": "none",
        "rows": "rows",
        "rowsType": "msg",
        "params": [],
        "x": 370,
        "y": 140,
        "wires": [
            [
                "f7d1eb556833dcdc"
            ]
        ]
    },
    {
        "id": "0d98e780fa3ef624",
        "type": "change",
        "z": "8cd81203fdd0b810",
        "name": "trans_time",
        "rules": [
            {
                "t": "set",
                "p": "topic",
                "pt": "msg",
                "to": "time",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 690,
        "y": 260,
        "wires": [
            [
                "8195c8281b400b36"
            ]
        ]
    },
    {
        "id": "f7d1eb556833dcdc",
        "type": "change",
        "z": "8cd81203fdd0b810",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "payload[0]",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 540,
        "y": 200,
        "wires": [
            [
                "0d98e780fa3ef624",
                "ad557ce11dbf4832"
            ]
        ]
    },
    {
        "id": "cb1eef534c2fdcd0",
        "type": "change",
        "z": "8cd81203fdd0b810",
        "name": "category",
        "rules": [
            {
                "t": "set",
                "p": "topic",
                "pt": "msg",
                "to": "category",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 480,
        "y": 280,
        "wires": [
            [
                "8195c8281b400b36"
            ]
        ]
    },
    {
        "id": "8195c8281b400b36",
        "type": "join",
        "z": "8cd81203fdd0b810",
        "name": "",
        "mode": "custom",
        "build": "object",
        "property": "payload",
        "propertyType": "msg",
        "key": "topic",
        "joiner": "\\n",
        "joinerType": "str",
        "accumulate": true,
        "timeout": "",
        "count": "2",
        "reduceRight": false,
        "reduceExp": "",
        "reduceInit": "",
        "reduceInitType": "",
        "reduceFixup": "",
        "x": 830,
        "y": 320,
        "wires": [
            [
                "ca542206a9977318",
                "ad557ce11dbf4832"
            ]
        ]
    },
    {
        "id": "ca542206a9977318",
        "type": "change",
        "z": "8cd81203fdd0b810",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "category",
                "pt": "msg",
                "to": "payload.category",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "start",
                "pt": "msg",
                "to": "payload.time.start",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "stop",
                "pt": "msg",
                "to": "payload.time.stop",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "id_machine",
                "pt": "msg",
                "to": "payload.time.id_machine",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 880,
        "y": 400,
        "wires": [
            [
                "df1850f112b05c82",
                "1547581edda91397",
                "5667dd82def58045"
            ]
        ]
    },
    {
        "id": "1547581edda91397",
        "type": "MSSQL",
        "z": "8cd81203fdd0b810",
        "mssqlCN": "75a62ffff4c55db2",
        "name": "Stop Category",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "query": "update machine_master set status_stop = 'True' where id_machine = 1\r\n\r\nIF NOT EXISTS (SELECT * FROM dbo.trans_stop WHERE (finish is null and id_machine = '1'))\r\nBEGIN\r\ninsert into trans_stop(id_machine) values (1)\r\nEND",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "editor",
        "paramsOpt": "queryParams",
        "paramsOptType": "none",
        "rows": "rows",
        "rowsType": "msg",
        "params": [],
        "x": 1200,
        "y": 400,
        "wires": [
            []
        ]
    },
    {
        "id": "6c45c54d852417a8",
        "type": "MSSQL",
        "z": "8cd81203fdd0b810",
        "mssqlCN": "75a62ffff4c55db2",
        "name": "Stop Category",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "query": "update trans_stop set category='{{{category}}}' where id_machine={{{id_machine}}} and start is NULL\r\n",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "editor",
        "paramsOpt": "queryParams",
        "paramsOptType": "none",
        "rows": "rows",
        "rowsType": "msg",
        "params": [],
        "x": 1160,
        "y": 500,
        "wires": [
            []
        ]
    },
    {
        "id": "df1850f112b05c82",
        "type": "debug",
        "z": "8cd81203fdd0b810",
        "name": "debug 25",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload.time.start",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1080,
        "y": 360,
        "wires": []
    },
    {
        "id": "ad557ce11dbf4832",
        "type": "debug",
        "z": "8cd81203fdd0b810",
        "name": "debug 26",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1000,
        "y": 240,
        "wires": []
    },
    {
        "id": "5667dd82def58045",
        "type": "delay",
        "z": "8cd81203fdd0b810",
        "name": "",
        "pauseType": "delay",
        "timeout": "5",
        "timeoutUnits": "seconds",
        "rate": "1",
        "nbRateUnits": "1",
        "rateUnits": "second",
        "randomFirst": "1",
        "randomLast": "5",
        "randomUnits": "seconds",
        "drop": false,
        "allowrate": false,
        "outputs": 1,
        "x": 960,
        "y": 460,
        "wires": [
            [
                "6c45c54d852417a8"
            ]
        ]
    },
    {
        "id": "75a62ffff4c55db2",
        "type": "MSSQL-CN",
        "tdsVersion": "7_4",
        "name": "",
        "server": "localhost",
        "port": "1433",
        "encyption": true,
        "trustServerCertificate": true,
        "database": "OEE",
        "useUTC": true,
        "connectTimeout": "1000",
        "requestTimeout": "10000",
        "cancelTimeout": "1000",
        "pool": "5",
        "parseJSON": false,
        "enableArithAbort": true
    }
]

```

Hi Steve,  
1.  
2. MSSQL-PLUS  
3. I'm triggering the datetime using OMRON FINS, if ON = trigger datetime (start), if OFF = trigger datetime (finish)

I am trying to copy the (start) data to another SQL table.

---

<div class="post-metadata">

### Author: ![cubical](https://avatars.discourse-cdn.com/v4/letter/c/f4b2a3/32.png) [@cubical](https://discourse.nodered.org/u/cubical)
#### Post date: [21 October 2022 08:11 UTC](https://discourse.nodered.org/t/copy-datetime-from-a-table-to-another-table/69354/4 "2022-10-21T08:11:28Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/a/0ac990e44becefefd9173aae856fea8b9e5e1b87.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/d/7d965a1a7ff4b870a833c1a0b8781515d5d9865e.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: [21 October 2022 08:42 UTC](https://discourse.nodered.org/t/copy-datetime-from-a-table-to-another-table/69354/5 "2022-10-21T08:42:53Z")

</div>

Hi, no offence @cubical but I guess you are not very proficient with node-red?

You seem to be branching off then re-joining when infact you can simply do everything in series...

### What you had...

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/7/f73200dde76fce78427279577d3f560d880bc227.png)

### What I propose

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/2/526519f8d25b5bf5be8812b76335e37aaab8590f.png)

Also, you appear to be avoiding the SQL Params & using mustache instead! why? Params are safe from SQL Injection hacks and IMO easer to use/understand.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/d/2d5476a761b7b9963d6560c8cd5636a07d889bd0.png)

  

### Test Flow

use `CTRL-I` to import (NOTE: Untested since I dont have your DB)

```auto
[{"id":"db30356beffb06d1","type":"inject","z":"8cd81203fdd0b810","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"No Operator","payload":"1","payloadType":"num","x":210,"y":140,"wires":[["275ac3bc4209aeed"]]},{"id":"275ac3bc4209aeed","type":"MSSQL","z":"8cd81203fdd0b810","mssqlCN":"75a62ffff4c55db2","name":"Start Time","outField":"payload","returnType":0,"throwErrors":1,"query":"select * from trans_time where id_machine = @machineID;","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"machineID","type":"int","valueType":"msg","value":"payload","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":370,"y":140,"wires":[["1547581edda91397","a36af554d806823b"]]},{"id":"1547581edda91397","type":"MSSQL","z":"8cd81203fdd0b810","mssqlCN":"75a62ffff4c55db2","name":"Stop Category","outField":"payload","returnType":0,"throwErrors":1,"query":"update machine_master \r\nset start = @start, category = @category\r\nwhere id_machine = @machineID\r\n\r\n","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"machineID","type":"Int","valueType":"msg","value":"payload[0].id_machine","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"start","type":"DateTime","valueType":"msg","value":"payload[0].start","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"category","type":"VarChar","valueType":"msg","value":"topic","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":540,"y":140,"wires":[["ad557ce11dbf4832"]]},{"id":"ad557ce11dbf4832","type":"debug","z":"8cd81203fdd0b810","name":"test#1 result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":710,"y":140,"wires":[]},{"id":"4120a6262867b6d2","type":"inject","z":"8cd81203fdd0b810","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"No Operator","payload":"1","payloadType":"num","x":210,"y":300,"wires":[["330e9050c608466c"]]},{"id":"330e9050c608466c","type":"MSSQL","z":"8cd81203fdd0b810","mssqlCN":"75a62ffff4c55db2","name":"Stop Category","outField":"payload","returnType":0,"throwErrors":1,"query":"declare @t datetime\r\nselect @t = start from trans_time where id_machine = @machineID\r\n\r\nupdate machine_master\r\nset start = @start, category = @category\r\nwhere id_machine = @machineID\r\n\r\n","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"machineID","type":"Int","valueType":"msg","value":"payload","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"category","type":"VarChar","valueType":"msg","value":"topic","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":380,"y":300,"wires":[["3036d374372b0f82"]]},{"id":"3036d374372b0f82","type":"debug","z":"8cd81203fdd0b810","name":"test#2 result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":550,"y":300,"wires":[]},{"id":"681ec23c81c08610","type":"comment","z":"8cd81203fdd0b810","name":"TEST # 1: get from DB, do some property moving/reshuffling, do the update query","info":"","x":420,"y":100,"wires":[]},{"id":"e60f84c64d27e1e9","type":"comment","z":"8cd81203fdd0b810","name":"TEST # 2: Do everything in one go","info":"","x":280,"y":260,"wires":[]},{"id":"a36af554d806823b","type":"debug","z":"8cd81203fdd0b810","name":"What do you see in here?","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":570,"y":200,"wires":[]},{"id":"75a62ffff4c55db2","type":"MSSQL-CN","tdsVersion":"7_4","name":"","server":"localhost","port":"1433","encyption":true,"trustServerCertificate":true,"database":"OEE","useUTC":true,"connectTimeout":"1000","requestTimeout":"10000","cancelTimeout":"1000","pool":"5","parseJSON":false,"enableArithAbort":true}]

```

---

<div class="post-metadata">

### Author: ![cubical](https://avatars.discourse-cdn.com/v4/letter/c/f4b2a3/32.png) [@cubical](https://discourse.nodered.org/u/cubical)
#### Post date: [21 October 2022 08:57 UTC](https://discourse.nodered.org/t/copy-datetime-from-a-table-to-another-table/69354/6 "2022-10-21T08:57:38Z")

</div>

Damn Steve, Thanks so much! And this is so much easier. Thanks 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: [4 November 2022 08:57 UTC](https://discourse.nodered.org/t/copy-datetime-from-a-table-to-another-table/69354/7 "2022-11-04T08:57:52Z")

</div>

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