Display days passed in decimal or integer in Ms SQL 2005 script

jana
jana used Ask the Experts™
on
We have a script that displays the difference between LastDate and FirstDate, but we can't get it to display a integer value, it display a Date format value.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
The script like a Accounts Receivables script:


DECLARE @ReportDate AS DATETIME
DECLARE @TimePass AS DECIMAL
SET @ReportDate = '20111231'
SET @TimePass = 30

SELECT FirstDate, LastDate, (LastDate-FirstDate)   FROM RM20101
WHERE LastDate <=@ReportDate
AND   (LastDate-FirstDate) > @TimePass

Open in new window


The problem is that the value displayed by "(LastDate-FirstDate)" is in Date Format.  Example (noticed the difference column):

FirstDate               LastDate                Differences
----------------------- ----------------------- -----------------------
2016-02-20 00:00:00.000 2016-03-22 00:00:00.000 1900-02-01 00:00:00.000
2016-03-27 00:00:00.000 1900-01-01 00:00:00.000 1783-10-07 00:00:00.000
2014-01-21 00:00:00.000 2014-02-20 00:00:00.000 1900-01-31 00:00:00.000

Open in new window

Author

Commented:
Where is the script going wrong?
use datediff

SELECT FirstDate, LastDate, datediff(d, FirstDate, LastDate)   FROM RM20101
WHERE LastDate <=@ReportDate
AND   datediff(d, FirstDate, LastDate) > @TimePass


http://msdn.microsoft.com/en-us/library/ms189794.aspx

Author

Commented:
Thank you very Much!!!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial