I have a stored procedure being executed by the MSSQL-Plus node that returns all the data as expected. However, the format of the date is not correct. I am sure this can be corrected with a function node or such, but before I go there, is there something obvious I am missing? In SSMS, the MSSQL stored procedure outputs the InvoiceDate and DueDate with the SQL format 'date', but for some reason in Node-RED it appears differently.
We expect the date to be 08-11-2021, but we get 2021-08-11T00:00:00.000Z
No, this is how the underlying driver converts the value from the SQL type to JavaScript.
JavaScript does not have a separate Date and DateTime type so it is only when you come to display it on a form should you worry.
What is important is what you are doing with it. As it stands, you are displaying it in a debug window. Technically, you know its correct but you want a different format. Why is that? If it is to be displayed in a dashboard table or written to a report document, then upon transmission to that other format you would format the Date object accordingly.
Thank you. It's being sent to a CSV file (gasp!!! I know, I know...it's a garbage format, but unfortunately the only type that Quickbooks accepts).
As a quick test, I used the Moment node for a given date object in my array (msg.payload[21].InvoiceDate) and it converted nicely as expected, but I do not know how to convert all the dates in the array so that they land in the CSV file formatted as MM-DD-YYYY. Below are the two views (using Excel...I know, another poor choice) to illustrate. Top view is how Quickbooks wants the dates. Bottom view is how I have them now. Bonus question is how I can remove "null" in the Location & Memo fields and have it be blank instead, but I can probably figure that one out on my own.
Have you tried formatting the date in the stored procedure SQL? I do something similar but since my db is mySQL, I am not sure what the syntax for MSSQL would be. In mySQL I use the date_format function as follows:
SELECT date_format(<date_field>,"%Y-%m-%d") FROM ......
To my surprise, I fixed this in one fell swoop by using the node-red-contrib-json2csv node. Dates are exactly as SQL outputs them and the null is no longer there in my csv file.
One last question: can someone explain how I can automatically populate the Parameters StartDate & EndDate with the date that the stored procedure is being run, rather than using the hard coded dates that I input into the Parameters section? (the two dates are always the same, i.e. I will run this at the end of each calendar day). I think it's simple but could not find a way to do this.
In the parameter list on the right hand side select the drop down & choose msg then enter the msg property that they appear in. If your dates are in flow or global context, you can select those as well.
