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.
| 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.56489SAS format z4.2 makes my number equal to 0034.56What'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 :) |
 |
|
|
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." |
|
|
|
|
|
|
|
|
Number formatting issue