small money data type query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

small money data type query

Author

Topic

neoice
Starting Member

7 Posts

Posted - 2009-01-08 : 16:16:13

Hi,

I am using sql 2005. I am using the small money data type for my 'cost' column. However, when data is entered I get 4 zeros after the decimal point, which looks odd. For example, I enter 34.50 and it appears as 34.5000

Am I missing something?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-01-08 : 17:39:50

No, that's just how sql server stores it. You can always format it the way you'd like to see it with your front end appl

Jim

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-08 : 22:42:23

Hi try this

select convert(smallmoney,4235.9800,1)
select convert(smallmoney,4235.9800,0)
Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-08 : 23:04:27

select cast(4235.9800 as smallmoney)

neoice
Starting Member

7 Posts

Posted - 2009-01-09 : 03:06:14

Hi, thanks for the replies!
How do I integrate it into my string? i am using VB/asp 2.net web front:
the field in question is 'cost'

Dim ad As New SqlClient.SqlDataAdapter("SELECT surname,first_name,initials,dept,code1,code2,code3,title,reasoning,date,provider,cost,value from Tblmain ", myconn)

I tried this but to no avail:

Dim ad As New SqlClient.SqlDataAdapter("SELECT surname,first_name,initials,dept,code1,code2,code3,title,reasoning,date,provider,convert(cost,4235.9800),value from Tblmain ", myconn)

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-09 : 03:23:45

quote:
Originally posted by neoice

Hi, thanks for the replies!
How do I integrate it into my string? i am using VB/asp 2.net web front:
the field in question is 'cost'

Dim ad As New SqlClient.SqlDataAdapter("SELECT surname,first_name,initials,dept,code1,code2,code3,title,reasoning,date,provider,cost,value from Tblmain ", myconn)

I tried this but to no avail:

Dim ad As New SqlClient.SqlDataAdapter("SELECT surname,first_name,initials,dept,code1,code2,code3,title,reasoning,date,provider,convert(cost,4235.9800),value from Tblmain ", myconn)

Dim ad As New SqlClient.SqlDataAdapter("SELECT surname,first_name,initials,dept,code1,code2,code3,title,reasoning,date,provider,convert(smallmoney,4235.9800,1),value from Tblmain ", myconn)

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-09 : 03:25:18

U can also try this

Dim ad As New SqlClient.SqlDataAdapter("SELECT surname,first_name,initials,dept,code1,code2,code3,title,reasoning,date,provider,convert(smallmoney,4235.9800,0),value from Tblmain ", myconn)

Jai Krishna

neoice
Starting Member

7 Posts

Posted - 2009-01-09 : 03:54:15

Thanks for the response. However, I still get the same error:

Type cost is not a defined system type.

I have also tried run this query directly on the sql server and get the same error.

any ideas?

cheers!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-09 : 03:57:18

convert the value into money r smallmoney
because cost is not asystem datatype

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-09 : 03:58:56

Dim ad As New SqlClient.SqlDataAdapter("SELECT surname,first_name,initials,dept,code1,code2,code3,title,reasoning,date,provider,convert(smallmoney,cost,0),value from Tblmain ", myconn)

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-09 : 04:00:00

Or u can also use this

Dim ad As New SqlClient.SqlDataAdapter("SELECT surname,first_name,initials,dept,code1,code2,code3,title,reasoning,date,provider,convert(smallmoney,cost,1),value from Tblmain ", myconn)

Jai Krishna

neoice
Starting Member

7 Posts

Posted - 2009-01-09 : 08:00:49

Hi,

Thanks so much for the help so far but i am still having issues. When running this from my application I recieve the following error:

A field or property with the name 'Cost' was not found on the selected data source.

if I run it directly on the SQL server it does format the data as I want however,the column now reads,"No Column Name".

cheers,

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-01-09 : 08:24:25

you need to alias the column name again i.e.

convert(smallmoney,cost,1) as cost

Em

neoice
Starting Member

7 Posts

Posted - 2009-01-09 : 08:59:12

Hi,

Ok, the error has gone and the format still stays the same when my app is run. If I run the query on SQL 2005 it works fine. I guess it is down to my ASP code:

<asp:BoundField DataField="Cost" HeaderText="Cost" SortExpression="Cost" />

Anyway, as this is an SQL forum I guess I should hunt around for a solution.

Many thanks to all, I have learned some neat SQL today.

neoice
Starting Member

7 Posts

Posted - 2009-01-09 : 10:15:06

just a quick update - I found the solution:

DataFormatString="{0:c}"

Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources