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 2012 Forums
 Transact-SQL (2012)
 Number formatting issue

Author  Topic 

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-11-05 : 18:01:21
I'm trying to translate code from SAS to SQL.
SAS has the following format I'm trying to convert:

My number 34.56489
SAS format z4.2 makes my number equal to 0034.56
What's the SQL equivalent code that can return this answer?

cooper2989
Starting Member

3 Posts

Posted - 2014-11-11 : 07:45:11
well this well get you the result but makes assumptions based on the data's format:

select right(REPLICATE('0',4) + cast(cast(round(34.56489,2) as float) as varchar(20)),7)

it first assumes your integers never exceed four digits in length and that you always requires 2 decimal places.

this will need alot of testing but should be a good basis for what youre after hopefully.

let me know how you get on :)
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2014-11-28 : 22:19:53
quote:
Originally posted by cooper2989

well this well get you the result but makes assumptions based on the data's format:

select right(REPLICATE('0',4) + cast(cast(round(34.56489,2) as float) as varchar(20)),7)

it first assumes your integers never exceed four digits in length and that you always requires 2 decimal places.

this will need alot of testing but should be a good basis for what youre after hopefully.

let me know how you get on :)




With the same assumptions as cooper's good solution above, you can cheat a bit...


SELECT RIGHT(CAST(34.56489 AS DECIMAL(6,2))+10000,7)


--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."
Go to Top of Page
   

- Advertisement -