I'm modifying a report that uses SQL Server to export data from package software (Yardi Voyager) into an MS Excel template. The data mostly comes over fine.
However on a few date fields the letter "N" is appended to the imported data and displayed in the cell in Excel.
(This is NOT #N/A - just "N")
For example, FORMAT(tbl.dtStart, 'dd/MM/yyyy') returns 31/01/2022 in interactive SQL, which is correct, but the value appearing in the Excel cell is: 31/01/2022N
Same for FORMAT(tbl.dtStart, 'dd/MM/yyyy', 'en-AU')
What I WANT is the usual number of days since 1/1/1900. (And then I'll format it in EXCEL) This value is needed for use in the next step in Excel.
CONVERT(Varchar, tbl.dtStart, 103)
will display 31/01/2022 correctly (no 'N') but is text only - not the number of days needed for the next step in Excel
I expect that this has something to do with how MS handles international characters and uses the prefix 'N' for NVARCHAR. But in all the research I've done I can not figure out how to simply eliminate the 'N' and get the number-date.
I suppose it could be something to do with the package software as well, but that would be strange.
I should also note that I have seen this on at least one text field as well - The letter "N" appended to the text value.
As I mentioned, I am modifying an existing report. The original code used
FORMAT(tbl.dtStart, 'dd/MM/yyyy')
successfully without any N. When I modified those lines the "N" began appearing.
Would greatly appreciate replies on how to export the date-number from SQL Server to Excel without the "N".
