Date format returned by MSSQL stored procedure

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

1 Like

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.

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