Solved

round off 101

Posted on 2005-04-28
273 Views
Hi,

How can we return this with only 2 trailing decimal places?  Y.XX

presently it's returning Y.XXXXXX

Thanks.
0
Question by:EdwardPeter

LVL 26

Accepted Solution

try
cast(nullif(datediff(n,#tempInout.DTstart,#tempinout.DTstop )/60.00,0) as decimal(10,2)) as LoginTime
0

Author Comment

Hilaire,

by the way, it will contain null, can we place 0 if null ?

Thanks.
0

LVL 26

Expert Comment

changed nullif to ISNULL
try

cast(ISNULL(datediff(n,#tempInout.DTstart,#tempinout.DTstop )/60.00,0) as decimal(10,2)) as LoginTime
0

Author Comment

Hilaire,

Generated .00 if null, everything else is good
0

LVL 26

Expert Comment

.00 = 0.00 = 0 in decimal(10,2) datatype

If you need it otherwise, you'll have to convert the numbers to strings, but this shsould be handled on the front-end side (the numbers formatting)
When you convert numbers to string, they don't sort nicely anymore
eg in alphabetical order '20' is bigger than '100'

If the .00 is really a problem, use

cast(cast(ISNULL(datediff(n,#tempInout.DTstart,#tempinout.DTstop )/60.00,0) as decimal(10,2)) as varchar(10)) as LoginTime

but I strongly suggest dealing with the presentation issues on the front-end side. Your programming langage most likely has a FormatNumber function/method
0

LVL 15

Assisted Solution

This just changes Hilaires function order around, I think it should work:

ISNULL(cast(datediff(n,#tempInout.DTstart,#tempinout.DTstop )/60.00 as decimal(10,2)),0) as LoginTime

No effort on my part - this is Hilaire's work - no points for me if it works!
0

Featured Post

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.