Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56

    Exclamation Unanswered: querying license that has been expired 4 200 days

    Ok, I have a date_expired field of the date that a license will expire. I would like to write a query for license's that have been expired for 200 plus days.

    I don't know what it is but I'm missing and not getting the info that I want.

    SELECT a.LNAME, a.FNAME, B.ID AS LIC_CONTACT_LINK_ID, C.LIC_NUMBER, C.LIC_TYPEDEF_ID, C.DATE_CREATED,
    E.DATE_EXPIRES, D.LIC_STATUS, D.WORK_STATUS
    --, DATEPART(DAY, getdate()) - DATEPART(DAY, E.DATE_EXPIRES) AS NO_OF_DAYS_LEFT

    FROM C_CONTACT_PEOPLE A,
    LIC_CONTACT_LINK B,
    LIC_HOLDERINFO C,
    LIC_STATUS D,
    LIC_TERMDEF_HOLDERINFO_LINK E
    WHERE a.ID = B.C_CONTACT_PEOPLE_ID
    AND B.ID = C.PRIMARY_CONTACT_LINK_ID
    AND C.ID = E.LIC_HOLDERINFO_ID
    AND C.STATUS_ID = D.ID
    and c.lic_typedef_id = 4
    AND C.STATUS_ID IN (SELECT distinct id
    FROM LIC_Status
    WHERE (License_Status <> 'PENDING'))
    --AND E.DATE_EXPIRES >= DATEDIFF (DAY, 200, getdate())
    order by LNAME

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DATEDIFF (DAY, 200, getdate()) ????

    You are getting DATEDIFF and DATEADD confused. DATEDIFF requires two date values as parameters, and one of the values you are supplying is actually an integer (200).

    Try this:
    WHERE E.DATE_EXPIRES <= DATEADD (DAY, -200, getdate())

    ...or this:
    WHERE DATEDIFF(day, E.DATE_EXPIRES, getdate()) >=200
    Last edited by blindman; 01-12-05 at 15:06.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56
    Those still didn't work!!!
    I have a specific license that I found that has been expired for 196 so I changed the to 100

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for the jolly factor, try:
    Code:
    SELECT *
       FROM LIC_TERMDEF_HOLDERINFO_LINK E
       WHERE E.DATE_EXPIRES <= DATEADD (DAY, -200, getdate())
    This gets the other tables out of the way, just to see if the row of interest turns up based on date.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The best way to debug is to simplify...
    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
  •