Help with number/money format

Transact-SQL
1

Good day

Please help.
I have a column 'CarHirePrem' that I've hardcoded in the script. The script works but when I copy the results into an excel sheet, I cannot sum the column as it's not in the number format.

How do I format this column in a script? please see the script below:

SELECT      dbo.TrnPolicy.ClientID + ''*'' + dbo.TrnPolicy.FileNo AS PolicyNo, dbo.TrnPolicy.Status AS PolicyStatus, 
            dbo.TrnPolicy.LinkID + '' '' + ISNULL(dbo.MstLink.LinkName, ''Unknown'') AS LinkIDName, dbo.TrnPolicy.InceptionDate, 
            dbo.TrnPolicy.RenewalDate,
            dbo.TrnPolicy.EndDate, dbo.TrnClient.Title, dbo.TrnClient.Initials, 
            dbo.TrnClient.ClientName AS Surname,  
            dbo.TrnPolicy.ResAddr1 AS StreetAddress, dbo.TrnPolicy.ResAddr2 AS StreetArea, dbo.TrnPolicy.ResAddr3 AS StreetCity, 
            dbo.TrnPolicy.ResPostalID AS StreetCode, dbo.TrnClient.ClientAddr1 AS PostalAddress, 
            dbo.TrnClient.ClientAddr2 AS PostalArea, 
            dbo.TrnClient.ClientAddr3 AS PostalCity, dbo.TrnClient.ClientPostalID AS PostalCode, 
            dbo.TrnClient.TelHome, dbo.TrnClient.TelWork, 
            dbo.TrnClient.TelCell, dbo.TrnClient.Email, 
            dbo.TrnRisk.VehMake, 
            dbo.TrnRisk.VehModel, TrnRisk.VehYear, 
            dbo.TrnRisk.Registration, dbo.TrnRisk.VinNumber, 
            TrnRisk.CoverID, MstCover.CoverDescr, ''70'' as CarHirePrem	

FROM          dbo.TrnPolicy INNER JOIN
                        dbo.TrnClient ON dbo.TrnPolicy.System = dbo.TrnClient.System AND dbo.TrnPolicy.ClientID = dbo.TrnClient.ClientID 
                        INNER JOIN TrnRisk ON dbo.TrnPolicy.System = dbo.TrnRisk.System AND dbo.TrnPolicy.ClientID = dbo.TrnRisk.ClientID AND
                        dbo.TrnPolicy.FileNo = dbo.TrnRisk.FileNo
                        LEFT JOIN MstCover ON dbo.MstCover.System = dbo.TrnRisk.System AND dbo.MstCover.CoverID = dbo.TrnRisk.CoverID   AND                    
							dbo.MstCover.SectionID = dbo.TrnRisk.SectionID
						LEFT JOIN MstLink ON dbo.MstLink.System = dbo.TrnPolicy.System AND dbo.MstLink.LinkID = dbo.TrnPolicy.LinkID 


WHERE       (dbo.TrnPolicy.Status IN (''A'', ''R'')) AND TrnRisk.RiskEndDate IS NULL 
AND TrnRisk.RiskStartDate IS NOT NULL
AND TrnRisk.SectionID = ''05''
AND TrnRisk.CoverID = ''01''
AND '+ @SelectClause + @WhereClause + @OrderByClause+'


					-- Use Select clause as per parameters supplied
Group By dbo.TrnPolicy.ClientID + ''*'' + dbo.TrnPolicy.FileNo, dbo.TrnPolicy.Status, 
            dbo.TrnPolicy.LinkID + '' '' + ISNULL(dbo.MstLink.LinkName, ''Unknown''), dbo.TrnPolicy.InceptionDate, 
            dbo.TrnPolicy.RenewalDate,
            dbo.TrnPolicy.EndDate, dbo.TrnClient.Title, dbo.TrnClient.Initials, 
            dbo.TrnClient.ClientName, 
            dbo.TrnPolicy.ResAddr1, dbo.TrnPolicy.ResAddr2, dbo.TrnPolicy.ResAddr3, 
            dbo.TrnPolicy.ResPostalID , dbo.TrnClient.ClientAddr1, 
            dbo.TrnClient.ClientAddr2, 
            dbo.TrnClient.ClientAddr3, dbo.TrnClient.ClientPostalID, 
            dbo.TrnClient.TelHome, dbo.TrnClient.TelWork, 
            dbo.TrnClient.TelCell, dbo.TrnClient.Email, 
            dbo.TrnRisk.VehMake, 
            dbo.TrnRisk.VehModel, TrnRisk.VehYear, 
            dbo.TrnRisk.Registration, dbo.TrnRisk.VinNumber, 
            TrnRisk.CoverID, MstCover.CoverDescr

Thanks
Matlotlo

2

You could try casting your hard-coded value as an INT/DECIMAL, although are you sure this isn't an issue with your excel?

SELECT	'70',
		CAST('70' AS INT),
		'70.0',
		CAST('70.0' AS DECIMAL(5,1))

I pasted that into a new workbook and was able to sum all 4 together with no issue

2 Likes
3

Thank you so much, it worked.

4

I have a similar issue. I execute a stored procedure and the results are saved in an Excel (.xlsx) file for email distribution. All the numeric values appear as text format in the file. I tried different data types in the stored proc i.e. INT, DECIMAL, NUMERIC but the results are still in text format. Is there anything I can do in the stored proc to change the output in Excel to number format?