I need a little advice for the Money datatype. When entering a value like $18.20 into a column of datatype Money, it stores and returns a value of 18.2 . It is set to the default paramaters. How can I change this so that it is correctly displayed as $18.20. My results are printed onscreen through VB, is that where I need to reformat?You sure it's money?
DECLARE @.x money
SELECT @.x = 18.20
SELECT @.x
EDIT: It's a presentation layer issue...
Then There Also
SELECT CONVERT(varchar(15),@.x,1)|||Positive it's Money|||Where's the sql being executed from? a stored procedure or called from a front end?
Did you try the code I posted in QA?|||It's a .asp page with VB. It's a dynamic SQL statment. And after running your code I got 18.2000. So it looks like it's good to 4 decimal places. This may be dumb to ask now, but should I kill that column and recreate it?|||You should be able to use the Convert function to format your output as a string, but formatting is best handled by your application interface.
blindman|||Do you know how to script the table so we can see the DDL?
Did you use the convert from the page?|||I'm a graphic designer doing the basics on MSSQL, I didn't even know you could script a table let alone tell you what a DDL is. But I'm a smart guy, I'll catch on quick.
And by converting do you mean something like CSTR to cast the returned value into a string or is it done within the SQL statment?
Originally posted by Brett Kaiser
Do you know how to script the table so we can see the DDL?
Did you use the convert from the page?|||SELECT CONVERT(varchar(15),@.x,1)
Just replace the @.x with the column name
Do you have SQL Server client tools installed on your desktop?
Do you know what Enterprise Manager is?|||Actually you should make the varchar(25) to handle all possible money values...
money and smallmoney
money
Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
smallmoney
Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.|||[i]Actually you should make the varchar(25) to handle all possible money values...
quote:
------------------------
money and smallmoney
money
Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
smallmoney
Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
------------------------
Brett ...
That sounds like an awful lot of Money. Hope the person having so much can lend some to me :D|||HAHA, after 5 minutes of staring at the wrong webpage I was working on, I got myself straightened out and found the right one. The Convert expression works like magic.
And yeah, i'm working in Enterprise Manager. I guess I'm a little above basic.|||I thought 15 was enough...but you still have to take in the right side of the decimal, the decimal itself, commas, and a sign...25
922 Trillion Dollars...|||Thanks for you help, I should be able to manage from here|||If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.|||Actually, it's more a matter of a development methodolgy...always code for what can happen...always...
That way there will never be any holes...|||Originally posted by stmaher
If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.
There go my hopes down the drain :D.
BTW Brett is right. The code should take care of all possible conditions.
Brett ... though I think varchar(15) will take care of Bill Gates wealth for sure|||Free forum !? What is your mailing address ? Or you can call the toll free number and submit your credit card information.|||What? Have NONE of you guys been receiving your checks?
Must be a problem in the payroll database...
blindman|||Just send me your SSN's and mailing addresses and I will get you on the payroll. If you receive any unexpected mail, like credit cards ..., ignore it and just forward them to me. :-)|||> That sounds like an awful lot of Money
hang on a sec...you must be assuming USD. I wonder whether the amount would still look so large if it was to refer to Japanese Yen. Not to mention some inflated currency from a developing country.|||You'd be surprized.
We have a thing in the IT dept. we call the "2 comma rule".
In other words, if the number (currency) doesn't have at least 2 commas, it isn't worth worrying about.
Swear to God.
Showing posts with label datatype. Show all posts
Showing posts with label datatype. Show all posts
Monday, February 20, 2012
Money to Varchar
From query analyzer how can I change the field datatype from money to varchar?
Alter table tablenaame alter column columnname varchar(30)--Is not working.Can you describe "not working" in a bit more detail? Does the command appear to run, but the column datatype doesn't change? Does it give an error message? Does your server crash and spit blue smoke?
-PatP|||You cannot change the datatype to money from (n)varchar, because this conversion cannot be done implicitly. You'll have to create a new table with desired datatype (money), use CONVERT/CAST function to insert the data into it, then drop the original table, create a new table with the same name, and insert the data back in.
Alter table tablenaame alter column columnname varchar(30)--Is not working.Can you describe "not working" in a bit more detail? Does the command appear to run, but the column datatype doesn't change? Does it give an error message? Does your server crash and spit blue smoke?
-PatP|||You cannot change the datatype to money from (n)varchar, because this conversion cannot be done implicitly. You'll have to create a new table with desired datatype (money), use CONVERT/CAST function to insert the data into it, then drop the original table, create a new table with the same name, and insert the data back in.
Money to Varchar
How can I alter a field datatype from money to varchar?To change the column type you will have to create a new table and copy the data over using the convert function, example below.
--create table x
--(a int null,
--b money null)
CREATE TABLE dbo.Tmp_x
(
a int NULL,
b nvarchar(50) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.x)
EXEC('INSERT INTO dbo.Tmp_x (a, b)
SELECT a, CONVERT(nvarchar(50), b) FROM dbo.x')
GO
DROP TABLE dbo.x
GO
EXECUTE sp_rename N'dbo.Tmp_x', N'x', 'OBJECT'
GO|||You can also add a temporary column to your table of the varchar type and fill it with the data from the money column to see if all values can be converted. If so, empty the money column, change the datatype of the money column to varchar and fill it with the data of the temporary column. Finally delete the temporary column.
To add a column:
alter table owner.your_table
add temp_column varchar(10)
To modify a column datatype:
alter table owner.your_table
alter column your_column varchar(10)
To drop a column:
alter table owner.your_table
drop column temp_column
--create table x
--(a int null,
--b money null)
CREATE TABLE dbo.Tmp_x
(
a int NULL,
b nvarchar(50) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.x)
EXEC('INSERT INTO dbo.Tmp_x (a, b)
SELECT a, CONVERT(nvarchar(50), b) FROM dbo.x')
GO
DROP TABLE dbo.x
GO
EXECUTE sp_rename N'dbo.Tmp_x', N'x', 'OBJECT'
GO|||You can also add a temporary column to your table of the varchar type and fill it with the data from the money column to see if all values can be converted. If so, empty the money column, change the datatype of the money column to varchar and fill it with the data of the temporary column. Finally delete the temporary column.
To add a column:
alter table owner.your_table
add temp_column varchar(10)
To modify a column datatype:
alter table owner.your_table
alter column your_column varchar(10)
To drop a column:
alter table owner.your_table
drop column temp_column
Money datatype Substring
I have a sql query listed below, I would like to count the number of values
that
the first 5 characters are '80438' from iNum field.
Please help me correct the sql query listed below.
Thank You,
SELECT count(iNum) from Call_Movement
where substring(cast(iNum as money) as char(20),1,5) like '80438'
iNum Money Format
iNum Data In Call_Movement Table
803482000146220.0000
803482000147143.0000
803482000153805.0000Try,
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
go
Expect a table or index scan.
AMB
"Joe K." wrote:
> I have a sql query listed below, I would like to count the number of value
s
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe
create table #test (c1 varchar(100))
insert into #test values ('80438hwdgyde5787kioui')
insert into #test values ('5838hwdgyde5787kioui')
insert into #test values ('80438mjji')
select count(c1) from #test where c1 like '80438%'
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A5FC4F29-928C-4D6D-B8F6-4A964D6711B2@.microsoft.com...
> I have a sql query listed below, I would like to count the number of
> values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||I received the following error when applying the query listed below.
Please help me resolve the query error listed below.
Thanks,
Error:
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.
SQL Query:
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
________________________________________
___________________________
"Joe K." wrote:
> I have a sql query listed below, I would like to count the number of value
s
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe,
SELECT count(iNum)
from Call_Movement
where convert(varchar(50), iNum, 0) like '80348%'
AMB
"Joe K." wrote:
[vbcol=seagreen]
> I received the following error when applying the query listed below.
> Please help me resolve the query error listed below.
> Thanks,
> Error:
> Implicit conversion from data type money to varchar is not allowed. Use th
e
> CONVERT function to run this query.
> SQL Query:
> SELECT count(iNum)
> from Call_Movement
> where ltrim(iNum) like '80348%'
> ________________________________________
___________________________
> "Joe K." wrote:
>
that
the first 5 characters are '80438' from iNum field.
Please help me correct the sql query listed below.
Thank You,
SELECT count(iNum) from Call_Movement
where substring(cast(iNum as money) as char(20),1,5) like '80438'
iNum Money Format
iNum Data In Call_Movement Table
803482000146220.0000
803482000147143.0000
803482000153805.0000Try,
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
go
Expect a table or index scan.
AMB
"Joe K." wrote:
> I have a sql query listed below, I would like to count the number of value
s
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe
create table #test (c1 varchar(100))
insert into #test values ('80438hwdgyde5787kioui')
insert into #test values ('5838hwdgyde5787kioui')
insert into #test values ('80438mjji')
select count(c1) from #test where c1 like '80438%'
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A5FC4F29-928C-4D6D-B8F6-4A964D6711B2@.microsoft.com...
> I have a sql query listed below, I would like to count the number of
> values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||I received the following error when applying the query listed below.
Please help me resolve the query error listed below.
Thanks,
Error:
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.
SQL Query:
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
________________________________________
___________________________
"Joe K." wrote:
> I have a sql query listed below, I would like to count the number of value
s
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe,
SELECT count(iNum)
from Call_Movement
where convert(varchar(50), iNum, 0) like '80348%'
AMB
"Joe K." wrote:
[vbcol=seagreen]
> I received the following error when applying the query listed below.
> Please help me resolve the query error listed below.
> Thanks,
> Error:
> Implicit conversion from data type money to varchar is not allowed. Use th
e
> CONVERT function to run this query.
> SQL Query:
> SELECT count(iNum)
> from Call_Movement
> where ltrim(iNum) like '80348%'
> ________________________________________
___________________________
> "Joe K." wrote:
>
Money datatype Substring
I have a sql query listed below, I would like to count the number of values
that
the first 5 characters are '80438' from iNum field.
Please help me correct the sql query listed below.
Thank You,
SELECT count(iNum) from Call_Movement
where substring(cast(iNum as money) as char(20),1,5) like '80438'
iNum Money Format
iNum Data In Call_Movement Table
803482000146220.0000
803482000147143.0000
803482000153805.0000Try,
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
go
Expect a table or index scan.
AMB
"Joe K." wrote:
> I have a sql query listed below, I would like to count the number of values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe
create table #test (c1 varchar(100))
insert into #test values ('80438hwdgyde5787kioui')
insert into #test values ('5838hwdgyde5787kioui')
insert into #test values ('80438mjji')
select count(c1) from #test where c1 like '80438%'
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A5FC4F29-928C-4D6D-B8F6-4A964D6711B2@.microsoft.com...
> I have a sql query listed below, I would like to count the number of
> values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||I received the following error when applying the query listed below.
Please help me resolve the query error listed below.
Thanks,
Error:
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.
SQL Query:
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
___________________________________________________________________
"Joe K." wrote:
> I have a sql query listed below, I would like to count the number of values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe,
SELECT count(iNum)
from Call_Movement
where convert(varchar(50), iNum, 0) like '80348%'
AMB
"Joe K." wrote:
> I received the following error when applying the query listed below.
> Please help me resolve the query error listed below.
> Thanks,
> Error:
> Implicit conversion from data type money to varchar is not allowed. Use the
> CONVERT function to run this query.
> SQL Query:
> SELECT count(iNum)
> from Call_Movement
> where ltrim(iNum) like '80348%'
> ___________________________________________________________________
> "Joe K." wrote:
> >
> > I have a sql query listed below, I would like to count the number of values
> > that
> > the first 5 characters are '80438' from iNum field.
> >
> > Please help me correct the sql query listed below.
> >
> > Thank You,
> >
> > SELECT count(iNum) from Call_Movement
> > where substring(cast(iNum as money) as char(20),1,5) like '80438'
> >
> > iNum Money Format
> >
> > iNum Data In Call_Movement Table
> > 803482000146220.0000
> > 803482000147143.0000
> > 803482000153805.0000
> >
that
the first 5 characters are '80438' from iNum field.
Please help me correct the sql query listed below.
Thank You,
SELECT count(iNum) from Call_Movement
where substring(cast(iNum as money) as char(20),1,5) like '80438'
iNum Money Format
iNum Data In Call_Movement Table
803482000146220.0000
803482000147143.0000
803482000153805.0000Try,
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
go
Expect a table or index scan.
AMB
"Joe K." wrote:
> I have a sql query listed below, I would like to count the number of values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe
create table #test (c1 varchar(100))
insert into #test values ('80438hwdgyde5787kioui')
insert into #test values ('5838hwdgyde5787kioui')
insert into #test values ('80438mjji')
select count(c1) from #test where c1 like '80438%'
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A5FC4F29-928C-4D6D-B8F6-4A964D6711B2@.microsoft.com...
> I have a sql query listed below, I would like to count the number of
> values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||I received the following error when applying the query listed below.
Please help me resolve the query error listed below.
Thanks,
Error:
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.
SQL Query:
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
___________________________________________________________________
"Joe K." wrote:
> I have a sql query listed below, I would like to count the number of values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe,
SELECT count(iNum)
from Call_Movement
where convert(varchar(50), iNum, 0) like '80348%'
AMB
"Joe K." wrote:
> I received the following error when applying the query listed below.
> Please help me resolve the query error listed below.
> Thanks,
> Error:
> Implicit conversion from data type money to varchar is not allowed. Use the
> CONVERT function to run this query.
> SQL Query:
> SELECT count(iNum)
> from Call_Movement
> where ltrim(iNum) like '80348%'
> ___________________________________________________________________
> "Joe K." wrote:
> >
> > I have a sql query listed below, I would like to count the number of values
> > that
> > the first 5 characters are '80438' from iNum field.
> >
> > Please help me correct the sql query listed below.
> >
> > Thank You,
> >
> > SELECT count(iNum) from Call_Movement
> > where substring(cast(iNum as money) as char(20),1,5) like '80438'
> >
> > iNum Money Format
> >
> > iNum Data In Call_Movement Table
> > 803482000146220.0000
> > 803482000147143.0000
> > 803482000153805.0000
> >
Money datatype- simple question!
Hi there!
In SQL server 2000, how do you change the number of decimal digits of money datatype field? By default it is 4 digits. I want to have only 2 digits after decimal point. Please help me!Instead of using money, you can use decimal with a scale of 2 or cast the money type to decimal with a scale of 2.|||Hi there!
Thanks for your reply. Now that I have changed the money datatype to decimal.
But the following line of code gives an 'Type Mismatch' error.
Dim Amt
Amt=rs("Rate")+rs("Interest") 'error in this line
Response.write Amt
'Rate' and 'Interest' are fields of decimal datatype.
rs - is a ADO recordset.
Can you tell me why? I have tried with 'Numeric' datatype also, and got the same error.|||Since your using VB, why not leave it as money and then use the FORMAT function?
MyValue = Format(DBValue,"###,###.##")
OR
In SQL Server you could use the CAST function on the MONEY field
try this
declare @.amt money
set @.amt = 0.2
select CAST(@.amt AS VARCHAR)|||It looks like you are doing asp with vbscript - it that correct ? I have tested it and it works fine. Do you have vb - if so test it with vb. How are you connecting to the database - post your code leading up to the recordset. Which version of sql server are you using - which version of ado/asp are you using ? Can you just set one of the field values equal to amount ?|||Hi rnealejr,
Thanks for the reply. Yes, you are right, I am using ASP with VBscript.
Version: SQL server 2000 & ASP3.0[IIS5.0].
Connection String: "Provider=SQLOLEDB;User ID=sa;password=xxx;Initial Catalog=myDb;Data Source=(local);"
set rs=server.createobject("ADODB.Recordset")
sql="Select * from myTable"
rs.open sql,con
if not rs.EOF then
Amt=rs("Rate")+rs("Interest") 'error in this line
end if
Can you just set one of the field values equal to amount ?
What do you mean by this? I didn't get you.
More Info: I am looking for a solution that is related with SQL server, rather than ASP. [because if the solution is ASP related, then I may have to change a lot of forms - around 150]
Please help me|||I will look into your code. Can you do the following:
Amt = rs("Rate")
and/or
Amt = rs("Interest")|||Hi,
Thanks again for your reply!
When I used Amt = rs("Rate"), it assigns the value.
In SQL server 2000, how do you change the number of decimal digits of money datatype field? By default it is 4 digits. I want to have only 2 digits after decimal point. Please help me!Instead of using money, you can use decimal with a scale of 2 or cast the money type to decimal with a scale of 2.|||Hi there!
Thanks for your reply. Now that I have changed the money datatype to decimal.
But the following line of code gives an 'Type Mismatch' error.
Dim Amt
Amt=rs("Rate")+rs("Interest") 'error in this line
Response.write Amt
'Rate' and 'Interest' are fields of decimal datatype.
rs - is a ADO recordset.
Can you tell me why? I have tried with 'Numeric' datatype also, and got the same error.|||Since your using VB, why not leave it as money and then use the FORMAT function?
MyValue = Format(DBValue,"###,###.##")
OR
In SQL Server you could use the CAST function on the MONEY field
try this
declare @.amt money
set @.amt = 0.2
select CAST(@.amt AS VARCHAR)|||It looks like you are doing asp with vbscript - it that correct ? I have tested it and it works fine. Do you have vb - if so test it with vb. How are you connecting to the database - post your code leading up to the recordset. Which version of sql server are you using - which version of ado/asp are you using ? Can you just set one of the field values equal to amount ?|||Hi rnealejr,
Thanks for the reply. Yes, you are right, I am using ASP with VBscript.
Version: SQL server 2000 & ASP3.0[IIS5.0].
Connection String: "Provider=SQLOLEDB;User ID=sa;password=xxx;Initial Catalog=myDb;Data Source=(local);"
set rs=server.createobject("ADODB.Recordset")
sql="Select * from myTable"
rs.open sql,con
if not rs.EOF then
Amt=rs("Rate")+rs("Interest") 'error in this line
end if
Can you just set one of the field values equal to amount ?
What do you mean by this? I didn't get you.
More Info: I am looking for a solution that is related with SQL server, rather than ASP. [because if the solution is ASP related, then I may have to change a lot of forms - around 150]
Please help me|||I will look into your code. Can you do the following:
Amt = rs("Rate")
and/or
Amt = rs("Interest")|||Hi,
Thanks again for your reply!
When I used Amt = rs("Rate"), it assigns the value.
money data-type query
I have a database table with a field of datatype "money".
I need to have this populated with rows that only have data up to a maximum
of two decimal places (i.e. pounds and pence).
However, I have come across some rows that have up to 3 significant figures
after the decimal points (i.e. 1/10th of a penny).
I need to identify all the rows that have more than two significant numbers
after the decimal place.
I thought:
SELECT * FROM myTable where
CAST(myField AS VARCHAR(20)) LIKE '%.[0-9][0-9][1-9]%'
would do the trick.
It doesn't.
Any suggestions would be really appreciated!
Thanks
GriffSorted:
WHERE (myField<> round(myField,2))
Griff|||"Griff" <Howling@.The.Moon> wrote in message
news:%23KcVFIIEGHA.140@.TK2MSFTNGP12.phx.gbl...
>I have a database table with a field of datatype "money".
> I need to have this populated with rows that only have data up to a
> maximum of two decimal places (i.e. pounds and pence).
> However, I have come across some rows that have up to 3 significant
> figures after the decimal points (i.e. 1/10th of a penny).
> I need to identify all the rows that have more than two significant
> numbers after the decimal place.
> I thought:
> SELECT * FROM myTable where
> CAST(myField AS VARCHAR(20)) LIKE '%.[0-9][0-9][1-9]%'
> would do the trick.
> It doesn't.
> Any suggestions would be really appreciated!
> Thanks
> Griff
>
Try this:
SELECT x
FROM tbl
WHERE x<>ROUND(x,2,1);
Be very careful when using MONEY for monetary amounts. In my opinion the
rounding errors caused by MONEY make DECIMAL a much better choice for
currency values in every case. See the example below. Do you have a good
excuse for using MONEY?
DECLARE
@.mon1 MONEY,
@.mon2 MONEY,
@.mon3 MONEY,
@.mon4 MONEY,
@.num1 DECIMAL(19,4),
@.num2 DECIMAL(19,4),
@.num3 DECIMAL(19,4),
@.num4 DECIMAL(19,4) ;
SELECT
@.mon1 = 100, @.mon2 = 339, @.mon3 = 10000,
@.num1 = 100, @.num2 = 339, @.num3 = 10000 ;
SET @.mon4 = @.mon1/@.mon2*@.mon3 ;
SET @.num4 = @.num1/@.num2*@.num3 ;
SELECT @.mon4 AS money_result,
@.num4 AS decimal_result ;
Result:
money_result decimal_result
-- --
2949.0000 2949.8525
(1 row(s) affected)
David Portas
SQL Server MVP
--|||The money data type stores data upto 4 decimal places. If you only require 2
decimal places you could consider storing the data as decimal or numeric
instead
HTH. Ryan
"Griff" <Howling@.The.Moon> wrote in message
news:OjWSPMIEGHA.344@.TK2MSFTNGP11.phx.gbl...
> Sorted:
> WHERE (myField<> round(myField,2))
> Griff
>
I need to have this populated with rows that only have data up to a maximum
of two decimal places (i.e. pounds and pence).
However, I have come across some rows that have up to 3 significant figures
after the decimal points (i.e. 1/10th of a penny).
I need to identify all the rows that have more than two significant numbers
after the decimal place.
I thought:
SELECT * FROM myTable where
CAST(myField AS VARCHAR(20)) LIKE '%.[0-9][0-9][1-9]%'
would do the trick.
It doesn't.
Any suggestions would be really appreciated!
Thanks
GriffSorted:
WHERE (myField<> round(myField,2))
Griff|||"Griff" <Howling@.The.Moon> wrote in message
news:%23KcVFIIEGHA.140@.TK2MSFTNGP12.phx.gbl...
>I have a database table with a field of datatype "money".
> I need to have this populated with rows that only have data up to a
> maximum of two decimal places (i.e. pounds and pence).
> However, I have come across some rows that have up to 3 significant
> figures after the decimal points (i.e. 1/10th of a penny).
> I need to identify all the rows that have more than two significant
> numbers after the decimal place.
> I thought:
> SELECT * FROM myTable where
> CAST(myField AS VARCHAR(20)) LIKE '%.[0-9][0-9][1-9]%'
> would do the trick.
> It doesn't.
> Any suggestions would be really appreciated!
> Thanks
> Griff
>
Try this:
SELECT x
FROM tbl
WHERE x<>ROUND(x,2,1);
Be very careful when using MONEY for monetary amounts. In my opinion the
rounding errors caused by MONEY make DECIMAL a much better choice for
currency values in every case. See the example below. Do you have a good
excuse for using MONEY?
DECLARE
@.mon1 MONEY,
@.mon2 MONEY,
@.mon3 MONEY,
@.mon4 MONEY,
@.num1 DECIMAL(19,4),
@.num2 DECIMAL(19,4),
@.num3 DECIMAL(19,4),
@.num4 DECIMAL(19,4) ;
SELECT
@.mon1 = 100, @.mon2 = 339, @.mon3 = 10000,
@.num1 = 100, @.num2 = 339, @.num3 = 10000 ;
SET @.mon4 = @.mon1/@.mon2*@.mon3 ;
SET @.num4 = @.num1/@.num2*@.num3 ;
SELECT @.mon4 AS money_result,
@.num4 AS decimal_result ;
Result:
money_result decimal_result
-- --
2949.0000 2949.8525
(1 row(s) affected)
David Portas
SQL Server MVP
--|||The money data type stores data upto 4 decimal places. If you only require 2
decimal places you could consider storing the data as decimal or numeric
instead
HTH. Ryan
"Griff" <Howling@.The.Moon> wrote in message
news:OjWSPMIEGHA.344@.TK2MSFTNGP11.phx.gbl...
> Sorted:
> WHERE (myField<> round(myField,2))
> Griff
>
money datatype
i have money a datatype in my table and it allows null
value. If i try to enter a null value from my asp page i
am getting error... what i need to do on my sql server
table so it does not error me out. i think, it has
something to do with precision and scale... but I don't
know what.
ERROR:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near ','.
ThanksCould you display the string that you are constructing in your application?
Look at this string when the money column is null. I am sure you will know
what's causing the problem. It just looks like you are concatenating
something with NULL and the whole thing becomes NULL.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"eren" <oeren@.calcoastcu.org> wrote in message
news:004c01c37d37$d3bd55e0$a401280a@.phx.gbl...
i have money a datatype in my table and it allows null
value. If i try to enter a null value from my asp page i
am getting error... what i need to do on my sql server
table so it does not error me out. i think, it has
something to do with precision and scale... but I don't
know what.
ERROR:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near ','.
Thanks|||This looks like a syntax error in the statement generated by the asp page.
Verify that the statement is correct and that nulls are handled correctly by
the asp code generating the statement.
Shaun
"eren" <oeren@.calcoastcu.org> wrote in message
news:004c01c37d37$d3bd55e0$a401280a@.phx.gbl...
> i have money a datatype in my table and it allows null
> value. If i try to enter a null value from my asp page i
> am getting error... what i need to do on my sql server
> table so it does not error me out. i think, it has
> something to do with precision and scale... but I don't
> know what.
> ERROR:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect syntax near ','.
> Thanks
value. If i try to enter a null value from my asp page i
am getting error... what i need to do on my sql server
table so it does not error me out. i think, it has
something to do with precision and scale... but I don't
know what.
ERROR:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near ','.
ThanksCould you display the string that you are constructing in your application?
Look at this string when the money column is null. I am sure you will know
what's causing the problem. It just looks like you are concatenating
something with NULL and the whole thing becomes NULL.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"eren" <oeren@.calcoastcu.org> wrote in message
news:004c01c37d37$d3bd55e0$a401280a@.phx.gbl...
i have money a datatype in my table and it allows null
value. If i try to enter a null value from my asp page i
am getting error... what i need to do on my sql server
table so it does not error me out. i think, it has
something to do with precision and scale... but I don't
know what.
ERROR:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near ','.
Thanks|||This looks like a syntax error in the statement generated by the asp page.
Verify that the statement is correct and that nulls are handled correctly by
the asp code generating the statement.
Shaun
"eren" <oeren@.calcoastcu.org> wrote in message
news:004c01c37d37$d3bd55e0$a401280a@.phx.gbl...
> i have money a datatype in my table and it allows null
> value. If i try to enter a null value from my asp page i
> am getting error... what i need to do on my sql server
> table so it does not error me out. i think, it has
> something to do with precision and scale... but I don't
> know what.
> ERROR:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect syntax near ','.
> Thanks
Subscribe to:
Posts (Atom)