Quick formatting Qs

[willydude]

How do I format numbers in SQL? A few examples:

I need a number that will keep the leading zeroes. When the user enters “0099” the number goes into the table and stays as “0099”. Or “0111” staying as “0111”. .

Also:

“1.450000000000” formats to “1.45000”. Five decimals to the right.

“1.45000001” formats to “1.45001”. Once again, five decimals to the right but rounds up.

Also dates:

“01-01-2001 12:00:00 AM” to “01-01-2001”

Thanks.

 

[AlexCuse]

I'm sure you could find a better one for the date by looking at one of the several date FAQ's, but have a look at these two queries:

select convert(decimal (10,8), 1.50)

select left(getdate(), 11)

Just replace getdate() and 1.50 with fields you are trying to format.

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[willydude]

Alex,

How does that work with an expression such as:

CASE WHEN PayFormula = 'PR' THEN dbo.tbl_TimesheetEntry.Units * dbo.tbl_JobsStepsTable.PrevailingWage / dbo.tbl_JobsStepsTable.Standard ELSE
0.0 END AS PieceRatePay

I am wanting to format the new field PieceRatePay. When I use

CASE WHEN PayFormula = 'PR' THEN dbo.tbl_TimesheetEntry.Units * dbo.tbl_JobsStepsTable.PrevailingWage / dbo.tbl_JobsStepsTable.Standard ELSE
0.0 END CONVERT(decimal(5, 8) AS PieceRatePay)

or

CASE WHEN PayFormula = 'PR' THEN dbo.tbl_TimesheetEntry.Units * dbo.tbl_JobsStepsTable.PrevailingWage / dbo.tbl_JobsStepsTable.Standard ELSE
0.0 END AS CONVERT(decimal(5, 8) PieceRatePay)

I get an error message about incorrect syntax near "CONVERT".

When I use

CASE WHEN PayFormula = 'PR' THEN CONVERT(decimal(5, 5), 
dbo.tbl_TimesheetEntry.Units * dbo.tbl_JobsStepsTable.PrevailingWage / dbo.tbl_JobsStepsTable.Standard) ELSE 0.0 END AS PieceRatePay

I get an error message "Arithmetic overflow error converting numeric to data type numeric." Same error with

CASE WHEN PayFormula = 'PR' THEN CONVERT(decimal(5, 5), 
(dbo.tbl_TimesheetEntry.Units * dbo.tbl_JobsStepsTable.PrevailingWage) / dbo.tbl_JobsStepsTable.Standard) ELSE 0.0 END AS PieceRatePay

Thanks.

 

[earthandfire]

I don't currently have QA available so I can't test this but I think:

CONVERT (decimal(10,8), 
  CASE WHEN PayFormula = 'PR' THEN
    dbo.tbl_TimesheetEntry.Units *
      dbo.tbl_JobsStepsTable.PrevailingWage /
      dbo.tbl_JobsStepsTable.Standard
  ELSE 0.0 
  END) AS PieceRatePay

should do what you need.


Hope this helps.

 

[willydude]

Earth,

With CONVERT (decimal(10,2), it does convert and round, but does not round up enough.

Examples: 1.8125 s/b 1.82, but it is rounding to 1.81. It is dropping the third digit. 1.6603 should round up to 1.67, but it also drops the third and fourth digits.

Anything in the third to eighth digit space, even if only “1”, should round up. (It’s a Department of Labor thing.)

I tried the Round function and had similar results. 1.8125 s/b 1.82, it is rounding to 1.81

Thanks for the help.

 

[SQLSister]

try the ceiling function instead of round

Questions about posting. See faq183-874

 

[willydude]

From what I have read, ceiling and floor are for returning integers only. Correct?

 

[earthandfire]

Using the following as an example:

DECLARE @sourceval float
DECLARE @resultval decimal(10,2)
SET @sourceval = 1.81000025 

SET
	@resultval = 
		FLOOR(@sourceval*100)/100 + 
			CASE
				WHEN PARSENAME(CONVERT(decimal(20,8), @sourceval * 100), 1) > 0 THEN 0.01 ELSE 0 END
SELECT @resultval

then

CONVERT (decimal(10,2), 
  CASE WHEN PayFormula = 'PR' THEN
		FLOOR 
    		(((dbo.tbl_TimesheetEntry.Units *
      			dbo.tbl_JobsStepsTable.PrevailingWage /
      			dbo.tbl_JobsStepsTable.Standard) * 100) / 100) + 
			CASE WHEN PARESENAME(CONVERT(decimal(20,8), 
    		(dbo.tbl_TimesheetEntry.Units *
      			dbo.tbl_JobsStepsTable.PrevailingWage /
      			dbo.tbl_JobsStepsTable.Standard) * 100), 1) > 0 THEN 0.01 
			ELSE 0 
			END		
  ELSE 0.0 
  END) AS PieceRatePay

should do what you want (assuming I matched the brackets correctly )

[tt]PARSENAME(1.23,1)[/tt] returns 23, so this does your calculation (truncated to 2 decimal places) then increments the final decimal place if any of the remaing decimal places in the original value are non zero

Hope this helps.