database size rounded and converted

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

database size rounded and converted

Author

Topic

Rayman
Starting Member

39 Posts

Posted - 2010-09-23 : 12:25:07

I am trying to produce a script to look at a table. Determine the database size and convert to integer from database_size. I am trying use convert with str function. Not sure if this is correct approach.

Table: Database_size

SQL2000 54.5
SQL2005 2000.78
SQL2000 12.4
SQL2005 234.32

Trying to round up to produce

SQL2000 55
SQL2005 2001
SQL2000 12
SQL2005 234

Any help would be appreciated. :-)

Terry Lynn King

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 12:30:48

Use ROUND(sizefield,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rayman
Starting Member

39 Posts

Posted - 2010-09-23 : 12:45:32

this did work for me when using the following
command

select database_size
from terri
where (database_size = Round(database_size, 1))

I wsa not clear sorry. I would like to look at all the values in the table and produce the results when not knowing the vaule. I would like to run this script against any database and retrieve the information. That is where I am tripped up. Thank you, TLKing
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[/quote]

Terry Lynn King

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 12:49:18

not sure how it worked for you but ROUND(54.5,1) gives the same value itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rayman
Starting Member

39 Posts

Posted - 2010-09-23 : 13:04:10

Yes, that was my example. Would like to exam the data regardless of
db and produce the desire output. Using the Function Round currently I have to place the number in the code. I am not sure if I am asking the question right.
But I would like to look at the table Terry_Lynn_size
and look at the numbers and them round up the numbers. I was trying to round and convert to integer from the terri_lynn_size. Do that sound correct? Sorry I am new to SQL.

quote:
Originally posted by visakh16

not sure how it worked for you but ROUND(54.5,1) gives the same value itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Terry Lynn King

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:06:35

ok thats fine. but rounding to integer you require ROUND(field,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rayman
Starting Member

39 Posts

Posted - 2010-09-23 : 13:41:29

Sorry, I lost the concepts in the thread. My code is giving me incorrect syntax errors. I am not sure where I went down the wrong path?

Terry Lynn King

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 13:43:27

Please post the code so that we can see where the syntax errors are.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:44:38

post the code then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rayman
Starting Member

39 Posts

Posted - 2010-09-23 : 13:49:56

SELECT datebase_size
FROM tery_lynn
WHERE (datebase_size = ROUND(345.494, 0))
ORDER BY datebase_size

This is not working. I do not get a display. Not sure if I am using the code correctly for my task?

Terry Lynn King

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:54:05

what does datebase_size field contain? whats the datatype of that field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 13:54:24

datebase_size? Or database_size?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Rayman
Starting Member

39 Posts

Posted - 2010-09-23 : 13:57:29

datebase_size is correct

quote:
Originally posted by tkizer

datebase_size? Or database_size?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Terry Lynn King

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 14:00:25

you didnt answer me yet

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 14:03:15

Post the exact error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Rayman
Starting Member

39 Posts

Posted - 2010-09-23 : 14:09:58

The column name is datebase_size
The script does not display errors. It does not return a value. I changed the vaules to see if I had the numeric_expression wrong or the length incorrect.

Syntax: Round (numerical_expression,Length[,Function])
What I am trying to do is add this to a select statement to view the size of the database and provide the size rounded up. I am new to this and appreciate all your help

Terry Lynn King

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 14:12:50

What does it display without the ROUND function?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Rayman
Starting Member

39 Posts

Posted - 2010-09-23 : 14:13:48

12.40
54.50
234.32
2254.78

Terry Lynn King

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 14:19:04

Don't you have the ROUND function in the wrong place?

SELECT ROUND goes here
FROM tery_lynn
WHERE datebase_size = Non-rounded value goes here
ORDER BY datebase_size

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Rayman
Starting Member

39 Posts

Posted - 2010-09-23 : 14:32:34

SELECT datebase_size
select round(54.50,1), round(2254.78,1), round(12.40,1), round(234.32,1)
FROM John

Msg 207, Level 16, State 1, Line 14
Invalid column name 'datebase_size'.

Terry Lynn King

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 14:35:31

SELECT ROUND(datebase_size, 1)
FROM YourTable
WHERE datebase_size = 54.50

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources