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 |
|
Bjcascone
Starting Member
37 Posts |
 Posted - 2008-12-05 : 09:35:30
|
| Hi, I am trying to look for data in a table that has been truncated. the field is a money field and what has happened is that while inserting the records, say the record was $200.00, after being inserted the record became $199.9999. I am trying to idetify any records that this has happened to. the catch is that some of the records that have been inserted should be out to the 4th decimal place ie $199.2134 these will usually not end in .9999. I am also searching through hundreds of millions of records and would prefer to search on numeric values rather than Alphanumeric for speed. Does anyone have any suggestions? thank you, Brian |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-05 : 09:45:40
|
When the value is stored in the MONEY datatype column, the value is rounded to four decimals.There is NO way to distinguish the "real value" 199.2134 from the "fake value" 199.2134Maybe you should restore an previous backup and update from that?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Bjcascone
Starting Member
37 Posts |
Posted - 2008-12-05 : 09:54:37
|
| i started off by selecting all of the money fields in a table declare @tablename varchar(50)set @tablename = [tableName]select * from sys.columnswhere object_id = (select object_id from sys.objects where name = @tablename)and scale > 2and system_type_id = 60this gave me a list of columns that have money fields. then i ran a simple select from those fields where the results where like '%999%'but this takes a long time. this may be my only option, and if so it is ok it gives me a data set to search through that. but i think there is a better way to do this. |
|
|
|
|
|
|
Decimal search help