Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

rounding in sql

Status
Not open for further replies.

daisypolly

Programmer
Apr 28, 2005
38
CA
I am having some problem with this query:

SELECT Jaso.dan_LOBTB028PATENT,
(DAYS(CURRENT_DATE)-DAYS(jason.datecreated))/30
FROM Video

In the line where the days
function starts this whole line is getting the diffrence between todays date
and the date in the column(Datecreated) field. Right now I am getting a
number like 12 or 6. I cannot get decimals so how can I get the decimals to
show also.For example like 12.6 or 6.3.





Thanks a lot,
daisy
 
You may try to force an implicit cast:
(DAYS(CURRENT_DATE)-DAYS(jason.datecreated))/30.0
or
1.0*(DAYS(CURRENT_DATE)-DAYS(jason.datecreated))/30

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
This is not working is there a function with I can do this
 
The standard way:
ROUND((DAYS(CURRENT_DATE)-DAYS(jason.datecreated))/30, 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
I tried this one also but does not work.any other ideas
 
Which RDBMS are you playing with ?
I don't think that DAYS is a standard function.
Your SQL is very strange as the selected columns seem coming from table jason but the from clause involves Video.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
thanks i think i made a typo there:

(DAYS(CURRENT_DATE)-DAYS(video.datecreated))/30 and DAYS is an SQL function.

daisy
 
DAYS is an SQL function
Which SQL flavor ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
So, try this:
DATEDIFF(jason.datecreated,CURRENT_DATE)/30

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
also i would like to know some thing else.
SELECT A.jason, B.Lidia FROM
Log.hih7868 A
Log.hih7868 B
Log.hih7868 C
Log.hih7868 D


I need to understand the from syntax. I understand that Log is a table and hih7868 is a field in that table but what does A stand for. Thansk for helping

 
well, for your first problem:
(DAYS(CURRENT_DATE)-DAYS(jason.datecreated))/30.0
works fine on other RDBMS.

for the second statement:
it selects a column called Jason from a table Log.hih7868,
joined with column Lidia from table Log.hih7868.
tablename here is fully qualified schema.table: log is the schema, hih7868 the table name.
the join condition is missing so you get a cross product, which is not very useful.
the other two tables are not selected from at all.
A, B, C, D are just table aliases

syntax is still:
select tableA.colA , tableB.colB from table A, table B
where tableA.colX = tableB.colX (or whatever condition)

so I am not sure your statement makes sense ...


Juliane
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top