Calculate number of days in between dates

[croiva25]

select a.RelocateID,a.DateEntered,a.CompanyID,b.FileClosedDate from test1 a inner join test b on
(a.RelocateID = b.RelocateID) where
CompanyID ='5710' and DateEntered >= '01/01/2004' and convert(varchar,FileClosedDate,101) < '31/12/2004'

Hi I was wondering if somebody could help me alter this query so I an calulate the difference in days in between DateEntered and FileClosedDate having the above criteria.
I can't seem to be able to get the datediff function right in this particular example.

 

[gmmastros]

You have the date '31/12/2004'. Could this simply bee a transposition of month and day. I.e. did you really want '12/31/2004'?

 

[r937]

you would not want to convert FileClosedDate to varchar

let the database compare dates as dates

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[croiva25]

Ok but unless I put the convert function I get the error The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

 

[JamesLean]

The reason for that error is, as gmmastros said, the way SQL Server is interpreting your date value. When specifying dates, always use a non-ambiguous format. I have also included the DATEDIFF function as per your original query:

SELECT
  a.RelocateID,
  a.DateEntered,
  a.CompanyID,
  b.FileClosedDate,
  [COLOR=red]DATEDIFF(dd, a.DateEntered, b.FileClosedDate)[/color]
FROM test1 a INNER JOIN test b ON a.RelocateID = b.RelocateID
WHERE CompanyID = '5710'
  AND [COLOR=red]DateEntered >= '20040101'[/color]
  AND [COLOR=red]FileClosedDate < '20041231'[/color]

--James