Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: problem with dates

    HI everybody,

    Had a problem with records of dates on a query I want to get the people who left the company with the year indicated 2010 it seems that the OR statement does not function because he left jan 10 2011 but disappeared already on the results of the query below...

    SELECT
    p.name
    FROM persons p
    inner join company c ON p.companyID=c.companyID
    WHERE
    (LEFT IS NULL) OR (DatePart(year,LEFT) = 2010)

    thanks for the help
    alex

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    LEFT is a keyword (function) in SQL Server. I don't believe you used LEFT as a column name in table? Eh? Select left(getdate(),5)

  3. #3
    Join Date
    Aug 2006
    Posts
    87
    sorry miquoted it it should be dateleft
    so the query goes like this

    SELECT
    p.name
    FROM persons p
    inner join company c ON p.companyID=c.companyID
    WHERE
    (LEFT IS NULL) OR (DatePart(year,DATELEFT) = 2010)

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by alexiop View Post
    sorry miquoted it it should be dateleft
    so the query goes like this

    SELECT
    p.name
    FROM persons p
    inner join company c ON p.companyID=c.companyID
    WHERE
    (LEFT IS NULL) OR (DatePart(year,DATELEFT) = 2010)
    First, when "LEFT is NULL" in one criteria it brings back all records where the results are NULL for LEFT (date?). Second, LEFT is a function and unless your column name is LEFT then an error is raised. I don't know why you would name a column on a key word? RIGHT, LEFT, JOIN, etc.. shouldn't be used to name columns. The second part of the criteria brings back records for people who left in 2010.

    If you only want people who left in 2010 then omit the first criteria.

  5. #5
    Join Date
    Aug 2006
    Posts
    87
    SELECT
    p.name
    FROM persons p
    inner join company c ON p.companyID=c.companyID
    WHERE
    (DATELEFT IS NULL) OR (DatePart(year,DATELEFT) = 2010)

    Sorry again overlooked the field name in left is null it should be dateleft is null which is the name of my field "sorry for the confusion" and thanks for your help

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    No problem Same results though. Dateleft is null returns all people who haven't left the company. I don't believe this is what you want?

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I am confused. You have:

    "...with the year indicated 2010 it seems that the OR statement does not function because he left jan 10 2011..."

    Since the person left in 2011 and you only want rows for any who left in 2010 (or NULL), you shouldn't get this person in the result set.

Posting Permissions

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