[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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.
0
EdwardPeter
Asked:
EdwardPeter
  • 3
  • 2
2 Solutions
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now