Results 1 to 6 of 6

Thread: Date Query NOT WORKING Right

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2004
    Location
    Alabama
    Posts
    126

    Date Query NOT WORKING Right

    The following query is supposed to select the EmployeeID and Hiredate along with number of years hired, year(now)-year(hiredate) as Years where the hire date is exactly 6 months ago or the current month. (The second part of the where clause appears to work. It simply says that years hired should be greater than 10)

    Six months ago would be November. The current month of course is May.

    However, this query identifies dates that appear on 12/1 which is an error.

    I'm assuming this is precision error. But I don't know.



    Code:
    SELECT EmployeeID, HireDate, year(now)-year(hiredate) as Years
    FROM Employee
    WHERE (Month(Now-HireDate)=6 Or Month(Now)=Month(HireDate)) And (year(now)-year(hiredate)>10);

    Query Results:

    Code:
    EmployeeID  HireDate	Years
    1	    11/9/1994	12
    10	    11/20/1991	15
    21	    11/16/1989	17
    88	    5/16/1989	17
    124	    12/1/1994	12
    133	    5/16/1991	15

  2. #2
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Date Query NOT WORKING Right

    Change

    WHERE (Month(Now-HireDate)=6 Or Month(Now)=Month(HireDate))

    to

    WHERE ((Month(Now) -Month (HireDate)) IN (6,-6) Or Month(Now)=Month(HireDate))
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Apr 2004
    Location
    Alabama
    Posts
    126

    Re: Date Query NOT WORKING Right

    Thanks! I'll give it a try! Why is there a difference? What went wrong?

    The above code that i used came from microsoft's website.

    Thanks again!

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Apr 2004
    Location
    Alabama
    Posts
    126

    Re: Date Query NOT WORKING Right

    That worked! Thanks!

    I'm still wondering why there was a difference. This is such an easily overlooked issued that it could cause problems if not thoroughly checked.

  5. #5
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Date Query NOT WORKING Right

    Month(Now-HireDate) is going to evaluate as follows

    1/ Determine the number of days between Now and HireDate. (giving an integer value of days)
    2/ Probably convert that integer value to a date, based on the 1st date in the system calendar
    3/ Determine the Month of that date.

    This is clearly goign to give wrong answers. My solution was to convert both dates to months and then subtract the resulting month numers.
    The resaon for the IN (6,-6) is that Jan (1) and July (7) are six months apart but would evalute to -6.
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Apr 2004
    Location
    Alabama
    Posts
    126

    Re: Date Query NOT WORKING Right

    Thanks very much!

    Great explanation.

Posting Permissions

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



Click Here to Expand Forum to Full Width