round off 101

Hi,

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

round(nullif(datediff(n,#tempInout.DTstart,#tempinout.DTstop )/60.00,0),2) as LoginTime

presently it's returning Y.XXXXXX

Thanks.
EdwardPeterAsked:
Who is Participating?
 
HilaireCommented:
try
cast(nullif(datediff(n,#tempInout.DTstart,#tempinout.DTstop )/60.00,0) as decimal(10,2)) as LoginTime
0
 
EdwardPeterAuthor Commented:
Hilaire,

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

Thanks.
0
 
HilaireCommented:
changed nullif to ISNULL
try

cast(ISNULL(datediff(n,#tempInout.DTstart,#tempinout.DTstop )/60.00,0) as decimal(10,2)) as LoginTime
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
EdwardPeterAuthor Commented:
Hilaire,

Generated .00 if null, everything else is good
0
 
HilaireCommented:
.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
 
mcmonapCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.