Conver format

[jannat]

Can anyone help on the following situation...

how to format a decimal lets say 5.40 into 000540?

Thanks!!

[nosepicker]

This sort of depends on how varied the numbers you are trying to convert are, but you can try this:

DECLARE @decimal_number decimal(10, 2)
SET @decimal_number = 5.40

SELECT REPLICATE('0', 6 - LEN(REPLACE(CONVERT(varchar, @decimal_number), '.', ''))) + REPLACE(CONVERT(varchar, @decimal_number), '.', '')

This code assumes that the length of your string is always going to be 6 characters long.