Can anyone figure out the problem here
var endDate = flow.get("endDate") || false;
//var endTime = flow.get("endTime") || false;
// check if date range is correct
if (startDate && endDate && new Date(endDate) >= new Date(startDate))
{
msg.topic = "SELECT * FROM [Dev].[dbo].[MQTTData] WHERE Timestamp BETWEEN 'startDate' AND 'endDate' ";
return msg;
} else
where is startDate?
tryonemoretime
[
{
"id": "2f8bfd2a84ecd1ae",
"type": "tab",
"label": "Flow 8",
"disabled": false,
"info": "",
"env": []
},
{
"id": "ddf7bdc1ff21ce85",
"type": "inject",
"z": "2f8bfd2a84ecd1ae",
"name": "query",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "query",
"payload": "",
"payloadType": "date",
"x": 170,
"y": 360,
"wires": [
[
"5c1e10ae15dfe082",
"a51468253ed967fb"
]
]
},
{
"id": "c548e007839fafe8",
"type": "debug",
"z": "2f8bfd2a84ecd1ae",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"statusVal": "",
"statusType": "auto",
"x": 1050,
"y": 360,
"wires": []
},
{
"id": "eac240528b89c9a5",
"type": "MSSQL",
"z": "2f8bfd2a84ecd1ae",
"mssqlCN": "df8c0b88.91b0a8",
"name": "",
"outField": "payload",
"returnType": 0,
"throwErrors": 1,
"query": "",
"modeOpt": "queryMode",
"modeOptType": "query",
"queryOpt": "payload",
"queryOptType": "editor",
"paramsOpt": "queryParams",
"paramsOptType": "none",
"rows": "rows",
"rowsType": "msg",
"params": [],
"x": 880,
"y": 360,
"wires": [
[
"c548e007839fafe8",
"b36ee57218125cf6"
]
]
},
{
"id": "5c1e10ae15dfe082",
"type": "change",
"z": "2f8bfd2a84ecd1ae",
"name": "",
"rules": [
{
"t": "set",
"p": "topic",
"pt": "msg",
"to": "download",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 310,
"y": 460,
"wires": [
[
"a51468253ed967fb"
]
]
},
{
"id": "9a3d637577bf2de2",
"type": "change",
"z": "2f8bfd2a84ecd1ae",
"name": "",
"rules": [
{
"t": "set",
"p": "startDate",
"pt": "flow",
"to": "$moment($$.payload).format(\"YYYY-MM-DD HH:mm:ss\")",
"tot": "jsonata"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 670,
"y": 100,
"wires": [
[
"b26398ef742a467e",
"a51468253ed967fb"
]
]
},
{
"id": "3f08de5283efc210",
"type": "change",
"z": "2f8bfd2a84ecd1ae",
"name": "",
"rules": [
{
"t": "set",
"p": "endDate",
"pt": "flow",
"to": "$moment($$.payload).format(\"YYYY-MM-DD HH:mm:ss\")",
"tot": "jsonata"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 670,
"y": 160,
"wires": [
[
"17d6e9f449142773",
"a51468253ed967fb"
]
]
},
{
"id": "b26398ef742a467e",
"type": "debug",
"z": "2f8bfd2a84ecd1ae",
"name": "debug 21",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 860,
"y": 100,
"wires": []
},
{
"id": "17d6e9f449142773",
"type": "debug",
"z": "2f8bfd2a84ecd1ae",
"name": "debug 22",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 860,
"y": 160,
"wires": []
},
{
"id": "8b58a03ce3f4a568",
"type": "moment",
"z": "2f8bfd2a84ecd1ae",
"name": "",
"topic": "",
"input": "payload",
"inputType": "msg",
"inTz": "Europe/Copenhagen",
"adjAmount": "1",
"adjType": "hours",
"adjDir": "subtract",
"format": "YYYY-MM-DD HH:mm:ss",
"locale": "dk",
"output": "payload",
"outputType": "msg",
"outTz": "Europe/Copenhagen",
"x": 420,
"y": 160,
"wires": [
[
"3f08de5283efc210"
]
]
},
{
"id": "c6077467d4fb818a",
"type": "moment",
"z": "2f8bfd2a84ecd1ae",
"name": "",
"topic": "",
"input": "payload",
"inputType": "msg",
"inTz": "Europe/Copenhagen",
"adjAmount": "1",
"adjType": "hours",
"adjDir": "subtract",
"format": "YYYY-MM-DD HH:mm:ss",
"locale": "dk",
"output": "payload",
"outputType": "msg",
"outTz": "Europe/Copenhagen",
"x": 420,
"y": 100,
"wires": [
[
"9a3d637577bf2de2"
]
]
},
{
"id": "f8b2b11cb7b65ea5",
"type": "inject",
"z": "2f8bfd2a84ecd1ae",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 220,
"y": 100,
"wires": [
[
"c6077467d4fb818a"
]
]
},
{
"id": "6c9e2c0d8381d677",
"type": "inject",
"z": "2f8bfd2a84ecd1ae",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 220,
"y": 160,
"wires": [
[
"8b58a03ce3f4a568"
]
]
},
{
"id": "9a7533924fe3e985",
"type": "ui_text_input",
"z": "2f8bfd2a84ecd1ae",
"name": "",
"label": "Start Time",
"tooltip": "",
"group": "e44f634b20faeabb",
"order": 6,
"width": 6,
"height": 2,
"passthru": true,
"mode": "datetime-local",
"delay": "0",
"topic": "topic",
"sendOnBlur": true,
"className": "",
"topicType": "msg",
"x": 170,
"y": 220,
"wires": [
[
"c6077467d4fb818a",
"841e01466cb98a6c"
]
]
},
{
"id": "2d7bf953862cd85b",
"type": "ui_text_input",
"z": "2f8bfd2a84ecd1ae",
"name": "",
"label": "End Time",
"tooltip": "",
"group": "e44f634b20faeabb",
"order": 8,
"width": 6,
"height": 2,
"passthru": true,
"mode": "datetime-local",
"delay": "0",
"topic": "endTime",
"sendOnBlur": true,
"className": "",
"topicType": "msg",
"x": 160,
"y": 280,
"wires": [
[
"8b58a03ce3f4a568",
"5356411ef9be38e2"
]
]
},
{
"id": "841e01466cb98a6c",
"type": "debug",
"z": "2f8bfd2a84ecd1ae",
"name": "debug 23",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 400,
"y": 220,
"wires": []
},
{
"id": "5356411ef9be38e2",
"type": "debug",
"z": "2f8bfd2a84ecd1ae",
"name": "debug 24",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 400,
"y": 280,
"wires": []
},
{
"id": "a51468253ed967fb",
"type": "function",
"z": "2f8bfd2a84ecd1ae",
"name": "My SQL Database Request",
"func": "// only execute query if download button has been clicked\nif(msg.topic == \"download\" || msg.topic == \"query\")\n{\n var startDate = flow.get(\"startDate\") || false;\n //var startTime = flow.get(\"startTime\") || false;\n \n var endDate = flow.get(\"endDate\") || false;\n //var endTime = flow.get(\"endTime\") || false;\n\n // check if date range is correct\n if (startDate && endDate && new Date(endDate) >= new Date(startDate))\n {\n msg.topic = \"SELECT * FROM [Dev].[dbo].[MQTTData] WHERE Timestamp BETWEEN ('\" + startDate + \"', 'AND' , '\" + endDate + \"')\"\n return msg;\n } else \n {\n node.warn(\"end date is before start date. or context not set\");\n }\n\n}\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 520,
"y": 360,
"wires": [
[
"2786afb55dc8df9b",
"eac240528b89c9a5"
]
]
},
{
"id": "b36ee57218125cf6",
"type": "ui_table",
"z": "2f8bfd2a84ecd1ae",
"group": "e44f634b20faeabb",
"name": "",
"order": 6,
"width": 0,
"height": 0,
"columns": [],
"outputs": 0,
"cts": false,
"x": 1070,
"y": 400,
"wires": []
},
{
"id": "2786afb55dc8df9b",
"type": "debug",
"z": "2f8bfd2a84ecd1ae",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"statusVal": "",
"statusType": "auto",
"x": 750,
"y": 460,
"wires": []
},
{
"id": "3522418d20bd200c",
"type": "inject",
"z": "2f8bfd2a84ecd1ae",
"name": "query",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "query",
"payload": "SELECT * FROM [Dev].[dbo].[MQTTData] WHERE Timestamp BETWEEN '2023-01-01 13:51:00' AND '2023-01-18 18:51:00'",
"payloadType": "str",
"x": 290,
"y": 600,
"wires": [
[
"eac240528b89c9a5"
]
]
},
{
"id": "df8c0b88.91b0a8",
"type": "MSSQL-CN",
"name": "Dev",
"server": "P202-LAERER",
"port": "1433",
"encyption": false,
"trustServerCertificate": false,
"database": "dev",
"useUTC": false,
"connectTimeout": "",
"requestTimeout": "",
"cancelTimeout": "",
"pool": "",
"parseJSON": false,
"enableArithAbort": true
},
{
"id": "e44f634b20faeabb",
"type": "ui_group",
"name": "Siemens",
"tab": "c781e4a970e06e4d",
"order": 1,
"disp": true,
"width": "12",
"collapse": false,
"className": ""
},
{
"id": "c781e4a970e06e4d",
"type": "ui_tab",
"name": "Home",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]
Are the values there
add more node.warn()'s
e.g.
// only execute query if download button has been clicked
if(msg.topic == "download" || msg.topic == "query")
{
node.warn(msg.topic)
var startDate = flow.get("startDate") || false;
//var startTime = flow.get("startTime") || false;
node.warn(startDate)
var endDate = flow.get("endDate") || false;
//var endTime = flow.get("endTime") || false;
node.warn(endDate)
// check if date range is correct
if (startDate && endDate && new Date(endDate) >= new Date(startDate))
{
msg.topic = "SELECT * FROM [Dev].[dbo].[MQTTData] WHERE Timestamp BETWEEN ('" + startDate + "', 'AND' , '" + endDate + "')"
return msg;
} else
{
node.warn("end date is before start date. or context not set");
}
}
And what is the out put of
new Date(endDate) and new Date(startDate)
Used youre flow
this is the result
"The input property was NOT a recognisable date. Output will be a blank string"
Feed it into a debug node set to show complete message and see what the query is.
Does the mssql node use the topic for the query? Which node are you using?
When i copy the debug sql statement to my sql and execute it it works 
im using ms-sql plus
Firstly, you are setting msg.topic but have the SQL node set to use the internal SQL editor.
Secondly, you should use parameters for this. Especially for dates & times as getting the correct format date string is often difficult.
The better way to do a request is enter the SQL in the mssql node & use parameters
Example..
SELECT * FROM [Dev].[dbo].[MQTTData]
WHERE
Timestamp BETWEEN (@startDate AND @endDate)
where @startDate & @endDate are entered in the "parameters" section & mapped to the correct property in the msg and set for the intended type.
Here is a previous working example
Thx for help
I can see now that it is your flow i'm working on 
i change msg.topic = "SELECT * FROM fuzzy WHERE datetime BETWEEN '"
to msg.payload = "SELECT * FROM fuzzy WHERE datetime BETWEEN '"
and now it all work without any change in the SQL thx for the input and the original flo
Reg
Peter
Doing it that way leaves your system open to attack by SQL injection, so don't do that if your node-red is accessible outside your local network. In fact you should get into the habit of doing it the safer way whether it is accessible or not.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy