sql decimal question 2

[03Explorer]

problem is I want to perform a simple calculation in SQL and have the result returned with 10ths decimal place populated... no more or less than that.

SELECT procedure, dob, (procedure - dob)/365 as age
FROM table

I find if I leave the 365 as whole number my returned value is whole value. if I put just a decimal place after 365 = '365.' then it returns 4 decimal places.

How can I return with 2 decimal places?

 

[DotNetGnat]

try this:

SELECT procedure, dob, (procedure - dob)/365.00 as age
FROM table

-DNG

 

[03Explorer]

.00 or .0 or even just . gives me the same many decimal places response.

XplorR

 

[DotNetGnat]

how about this:

SELECT procedure, dob, Cast((procedure - dob)/365 as decimal(3,2)) as age
FROM table

-DNG

 

[03Explorer]

I get an error message from sybase that says "Select error: SQLSTATE = 22003 [Sybase][ODBC Driver][Adaptive Server Anywhere]value 50 out of range for destination" with an OK button.

I checked both Proc_date & dob for null's and there are none.

 

[pmegan]

It may be a bit of a resource hog but try this

SELECT procedure, dob, cast(round(((procedure - dob)/365.),2) as decimal(6,2)) as age
FROM table

 

[DotNetGnat]

i suggested decimal(3,2) because i dont think anyone can live for an age which will be a 6 digit number...

-DNG

 

[pmegan]

DNG - my bad. I somehow managed to miss your post, didn't mean to repeat it. I was playing with a financial export with a (6,2) when I saw this thread and missed that change.

 

[03Explorer]

Apologies for the delayed response. It works just as expected. Thank you for your help.

 

[03Explorer]

I ended up using:

SELECT procedure, dob, cast(round(((procedure - dob)/365.),2) as decimal(3,2)) as age 
FROM table

Thanks again

 

[DotNetGnat]

hmm...very old post...but no problem...glad to be of help...

-DNG