Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: Problem in SELECT with DATE RANGE

    Hi..

    I got a problem in my SELECT Statement:

    Code:
    SELECT DISTINCT IF(ISNULL(a.LOGIN), 'rdc', '') AS LOGIN_CLASS, IF(ISNULL(a.LOGOUT), 'rdc', '') AS LOGOUT_CLASS, a.EMP_NO, a.LOGIN, a.LOGOUT, CONCAT(LNAME, ',' , FNAME, ' ', MI, '.') AS FULLNAME
     FROM attendance.employee_attendance AS a JOIN hris.employment em ON (a.EMP_NO = em.EMP_NO AND em.STATUS = 'Reg Operatives') JOIN hris.personal AS p ON p.EMP_ID = em.EMP_ID
     WHERE a.LOGIN BETWEEN '2012-01-16' AND '2012-02-01' OR a.LOGOUT BETWEEN '2012-01-16' AND '2012-02-01' 
     OR ISNULL(a.LOGIN) OR ISNULL(a.LOGOUT) ORDER BY FULLNAME, a.LOGIN
    I attach the 2 image, first the sample attendance which I get the data base on my select query, and the sample get attendance which is the output.

    I notice that in the output from my query, the attendance with 2012-02-01 that has no logout did not displayed and also the NO LOGIN and LOGOUT .

    when i tried to select from 2012-01-16 t0 2012-02-02, the date 2012-02-01 was displayed., but I need to range it in 2012-01-16 to 2012-02-01 only.

    Thank you

    Thank you..
    Attached Thumbnails Attached Thumbnails sample attendace.JPG   sample get attendance.JPG  

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Firstly, what datatype are you storing the a.LOGIN and a.LOGOUT fields? If this is datetime then you have to remember that this will contain both a date and a time for that date. So asking something between 01 and 02 will return anything after 01 at 00:00:00 and upto and including 02 00:00:00. So if you are searching for something added on the 2012-02-01 at 13:00:00 then you need to search greater than 2012-01-16 00:00:00 and less or equal to 2012-02-01 23:59:59.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by it-iss.com View Post
    Firstly, what datatype are you storing the a.LOGIN and a.LOGOUT fields? If this is datetime then you have to remember that this will contain both a date and a time for that date. So asking something between 01 and 02 will return anything after 01 at 00:00:00 and upto and including 02 00:00:00. So if you are searching for something added on the 2012-02-01 at 13:00:00 then you need to search greater than 2012-01-16 00:00:00 and less or equal to 2012-02-01 23:59:59.
    yes, it is datetime, so what should I need to revise in my query?
    or what should I need to input in my date range?

    Thank you

  4. #4
    Join Date
    Dec 2010
    Posts
    136
    I tried to range 2012-01-16 00:00:00 to 2012-02-01 23:59:59 and it display the 2012-02-01 attendance,

    but is there any solution to display the 2012-02-01 but no need to input 00:00:00 and 23:59:59 after the date?

    cause I think its hazard to input a long data and also I use calendar to choose date.

    Thank you

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHERE   start_date >= '20120116'
    AND     start_date <  '20120117'
    George
    Home | Blog

  6. #6
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by gvee View Post
    Code:
    WHERE   start_date >= '20120116'
    AND     start_date <  '20120117'
    How about the end_date or logout?

    Thank you

Posting Permissions

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