Decimal Datatype with negative value

[gussy81]

I have a strange one. I am using the datatype DECIMAL in my SQL table. like this:

DebtAmount DECIMAL(17,2) NULL

If I insert values like: 100 or 10.5 or -10 they insert no problem.

The problem I am having is loads of my data has negative values that have it like 200- or 350- (you see the minus sign at the end of the value instead of before it)

When i bring them into excel they come in no problem with the minus sign before the value but SQL does not like the minus figure after the value. i cant really do anything about how the values import from our system this way so i was hoping i could work with sql on this issue.

error i get is: Error converting data type varchar to numeric.

Thanks

G

 

[Chris Miller]

Well, then you likely work with CSV source files, do you? And if they have 200- in them, that won't import into a DECIMAL or other numeric field types.

You will need what's called a staging table, that term is what you could google, that is having data types to which you can import CSV and then convet from there. So this field in the taging table should be varchar(17) or perhaps even varchar(20) to make sure nothing is cut off. Then convert a number with trailing minus sign to an actual numeric type, for example like this:

CREATE TABLE Staging (charnum varchar(20));
  
INSERT INTO Staging (charnum) VALUES ('987.65-'),('-123.45');

Select IIF(CHARINDEX('-',charnum,1)>0,-1,1)*Cast( Trim(BOTH '-' FROM charnum) as decimal(17,2)) as resultcolumn  FROM Staging;

The IIF determines the factor -1 or 1 based on finding a minus sign in the varchar field at any position (CHARINDEX(...)>0 means the '-' is found), the CAST converts the value with minus characters removed.

Chriss

 

[gussy81]

fantastic Chriss i will try this thanks

 

[gbaughma]

At first glance, I was trying to figure out how you were preserving whether it was negative or positive.

Then I took a closer look at your IIF. Quite clever. Good job!


Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[Andrzejek]

you likely work with CSV source files, do you?

That would be nice to know.

If that is the case, you can always write a little app (in any language, VBA?) to convert your CSV file:

[pre]
ABC,500,[red]250-,200-,350-[/red],-10
XYZ,-800,340,6,[red]357-[/red],10
[/pre]
into something you can easily consume:

[pre]
ABC,500,[red]-250,-200,-350[/red],-10
XYZ,-800,340,6,[red]-357[/red],10
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

Of course, it depends what you're familiar with. I could assume unfimilarity with SQL Server, when a concept like staging table is unknown to gussy81, because it's in general a way to first just import what's in the origin 1:1 and then use your T-SQL datatype knowledge, knowledge about CAST and CONVERT and other T-SQL functions like I used, to convert after input.

Maybe I'm too fast in my judgment thinking lack of T-SQL knowledge means lack of other programming knowledge, too. I think the batallion of functionalities you have in T-SQL is always fitting any needs, even unconventional (at least in SQL terms) numeric formats.

Regarding dates the CONVERT function shows MSSQL knows many standards it can convert from a character represnetation to one of it's date/time/datetime data types. There's nothing so specialized about numbers as they are also usually not formatted in such a unusual way. And even with an extension of the CONVERT function capabilities - quite feasable, if you think that Microsoft wants to support many standards to make imports easy - it likely won't make it into implicit conversion without explicitly calling the CONVERT() function, i.e. it's not feasable to interpret a string like '100-' as -100, even if you know about accounting debt notation. Excel obviously does that. I haven't tried it out. So you could argue software of MS isn't consistent. Well, always keep in mind MS did not develop anything it now has in mainly Windows, Office applications and SQL Server from scratch by itself with a consistent code base across all applications and servers. IIRC the Office applications were even put together from different acquisations, so just because Excel makes 100- to -100, doesn't mean every MS software does.

So, last not least, the problem could likely also be solved in the output of such data to the much more conventional sign before number format, especially when the output is to CSV, CSV is not an accounting standard. It's a standard lacking some standardization, and as any combination of characters can always be a string, there's nothing that would trigger a warning or error if you output ...,100-,... into a CSV file. There's usually not even an association of CSV with database software, I thikn the default file association is notepad or Execl, once you install Office, but eve n with such an association a software outputtting a file with a specific file extensions doesn't pull up an instance of associated software to be a witness and verificator of the correctness of the file content. So, in short, while it's possible to finally have sofware like Excel importing 100- as -100, you expect too much of such a standardization and you better convert to norms that are more widespread, if you output data for transfer to other systems.

So, that is a task that's well justified to be a burden for the output of such data to exchange files than it is your task to handle it in input. Of course, there's no point in demanding the data exchange format, but you can argue for a better exchange file, on the basis it surely is less work to change the export than your import. One thing I can assure, when they use their own exports to import them in Excel: Excel will import -100 as -100 , too.

Chriss