adding thousandth comma in number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

adding thousandth comma in number

Author

Topic

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-05-11 : 11:12:50

I have


DECLARE @From DATE = DATEADD(MONTH, -12, GETDATE()),
	@To Date = GETDATE(),
	@LLT_DAYS Int

select TOP 1 @LLT_DAYS = (LLT_DAYS)
FROM tbl_HAW_HealthAndSafety_LLT
ORDER BY eveEventDate DESC

SELECT SUM(hsmTotalHoursWorkedByHourlyEmp + hsmTotalHoursWorkedBySalariedEmp) /365 * @LLT_DAYS AS WHSLLT
  FROM tbl_HealthAndSafetyMonthlyData
  WHERE Convert(date,hsmReportingDate) BETWEEN @From AND @To
GO

That gives me 629310.093144 and I need the result to be 629,310
I have tried CAST to get rid of the after the decimal but couldn't get it to work right in using SUM. Hoping there is a way also to put the ',' placeholder in inside of sql. This ends up on an asp page so I maybe could do it there, but would rather do it here. Thanks for the beginner help.

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-05-11 : 11:17:43

I tried


DECLARE @From DATE = DATEADD(MONTH, -12, GETDATE()),
	@To Date = GETDATE(),
	@LLT_DAYS Int

select TOP 1 @LLT_DAYS = (LLT_DAYS)
FROM tbl_HAW_HealthAndSafety_LLT
ORDER BY eveEventDate DESC

SELECT CAST(SUM(hsmTotalHoursWorkedByHourlyEmp + hsmTotalHoursWorkedBySalariedEmp) /365 * @LLT_DAYS AS char(6))  
  FROM tbl_HealthAndSafetyMonthlyData
  WHERE Convert(date,hsmReportingDate) BETWEEN @From AND @To
GO

But get "Arithmetic overflow error converting numeric to data type varchar"

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-05-11 : 11:24:50

I am getting there but still have the comma question


DECLARE @From DATE = DATEADD(MONTH, -12, GETDATE()),
	@To Date = GETDATE(),
	@LLT_DAYS Int

select TOP 1 @LLT_DAYS = (LLT_DAYS)
FROM tbl_HAW_HealthAndSafety_LLT
ORDER BY eveEventDate DESC

SELECT CAST(SUM(hsmTotalHoursWorkedByHourlyEmp + hsmTotalHoursWorkedBySalariedEmp) /365 * @LLT_DAYS AS numeric(6,0))  
  FROM tbl_HealthAndSafetyMonthlyData
  WHERE Convert(date,hsmReportingDate) BETWEEN @From AND @To
GO

Returns 629310
How do I return 629,310

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-11 : 11:34:30

Most people on this forum would strongly advise you to not do this conversion in SQL and instead do it where the data will be consumed - for example the presentation layer. However, if you do want to do it in SQL, take a look at the discussion here: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/49e7d563-5056-478a-921d-ec56ae3162c8/

I have to sheepishly admit that I have been forced into doing this type of thing in SQL, so your call.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-14 : 07:46:16

Where do you want to show data? If you use front end application do the formation there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources