rounding in sql

[daisypolly]

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

 

[PHV]

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

 

[daisypolly]

This is not working is there a function with I can do this

 

[PHV]

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

 

[daisypolly]

I tried this one also but does not work.any other ideas

 

[PHV]

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

 

[daisypolly]

thanks i think i made a typo there:

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

daisy

 

[PHV]

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

 

[daisypolly]

MySQL.

 

[PHV]

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

 

[daisypolly]

thanks how would I display decimals.

 

[daisypolly]

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

 

[juliane26]

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