# Date format returned by MSSQL stored procedure
**URL:** https://discourse.nodered.org/t/date-format-returned-by-mssql-stored-procedure/49615
**Category:** General
**Created:** [11 August 2021 13:57 UTC](https://discourse.nodered.org/t/date-format-returned-by-mssql-stored-procedure/49615 "2021-08-11T13:57:58Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@grant1](https://discourse.nodered.org/u/grant1)
#### Post date: [11 August 2021 13:57 UTC](https://discourse.nodered.org/t/date-format-returned-by-mssql-stored-procedure/49615/1 "2021-08-11T13:57:58Z")
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
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [11 August 2021 14:24 UTC](https://discourse.nodered.org/t/date-format-returned-by-mssql-stored-procedure/49615/2 "2021-08-11T14:24:58Z")
> [@grant1](#):
>
> is there something obvious I am missing?
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.
---
### Author:  [@grant1](https://discourse.nodered.org/u/grant1)
#### Post date: [11 August 2021 15:12 UTC](https://discourse.nodered.org/t/date-format-returned-by-mssql-stored-procedure/49615/3 "2021-08-11T15:12:52Z")
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.
---
### Author:  [@rakgupta](https://discourse.nodered.org/u/rakgupta)
#### Post date: [11 August 2021 16:13 UTC](https://discourse.nodered.org/t/date-format-returned-by-mssql-stored-procedure/49615/4 "2021-08-11T16:13:25Z")
> [@grant1](#):
>
> 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.
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(\,"%Y-%m-%d") FROM ......
---
### Author:  [@grant1](https://discourse.nodered.org/u/grant1)
#### Post date: [12 August 2021 01:37 UTC](https://discourse.nodered.org/t/date-format-returned-by-mssql-stored-procedure/49615/5 "2021-08-12T01:37:56Z")
To my surprise, I fixed this in one fell swoop by using the [node-red-contrib-json2csv](https://flows.nodered.org/node/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.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [12 August 2021 06:10 UTC](https://discourse.nodered.org/t/date-format-returned-by-mssql-stored-procedure/49615/6 "2021-08-12T06:10:16Z")
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.
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [26 August 2021 06:11 UTC](https://discourse.nodered.org/t/date-format-returned-by-mssql-stored-procedure/49615/7 "2021-08-26T06:11:12Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.