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

Looks like you are dealing with UDFs. Show the code for them.

no I am not using user defined function:
(DAYS(CURRENT_DATE)-DAYS(video.datecreated))/30

There is just one typo instead of jason it should have been video and DAYS and CURRENT_DATE is a function in SQL language.

Thanks
Daisy

CURRENT_DAYS and DAYS are not SQL language functions. They must be specific to the SQL Server you are using.

http://www.oreilly.com/catalog/sqlnut/chapter/ch04.html

The functions are listed on this site.

The DAY function (that's what you meant, right? There is no DAYS function) returns the day portion of a datetime as an integer. Since you are computing the difference between two integers, the result is an integer. You then divide by an integer (30) so by default the division is integer division and the result is an integer. This conversion truncates any fractional part.

Try dividing by a decimal number to force the expression to be evaluated as a decimal number instead; your expression would then be something like:
This by default give the result to 4 decimal places; you can use CAST/CONVERT to alter the precision of the result.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

BTW, I just looked a bit closer at your expression and realized it almost certainly will not give you what you are probably looking for. The DAY function simply returns the day portion of a date. You are computing the difference between the day of the month of one date with the day of the month of another. This will be a negative number sometimes. If you compute your expression early in the month aginst a date later in its month, the result would be negative. If you happen to execute the expression on the same day of the month as your other date, even if it is in a diferent month and years apart, the result would be zero.

I think what you may want to use instead is the DATEDIFF function, as:

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

ok thanks for the help I will try this.