Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Unanswered: cal number of days in between dates

    select a.RelocateID,a.DateEntered,a.CompanyID,b.FileClose dDate 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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why are you converting FileClosedDate to varchar?

    let the database compare dates as dates

    what are the datatypes of the two date columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    23
    Try using # Sign before and After Dates like
    select a.RelocateID,a.DateEntered,a.CompanyID,b.FileClose dDate 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#

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by mfdarvesh
    Try using # Sign before and After Dates like
    select a.RelocateID,a.DateEntered,a.CompanyID,b.FileClose dDate 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#
    This is the SQL Server forum, not the MS Access forum, darvesh...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •