Problem in SELECT with DATE RANGE

[newphpcoder]

Hi..

 

I got a problem in my SELECT Statement:

 

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..

[kickstart]

Hi

 

Think the problem is that your columns are date time fields and you are comparing them with dates. MySQL is taking 2012-02-01 and interpreting that as 2012-02-01 00:00:00. Which means 2012-02-01 05:25:00 is outside that range.

 

Would still expect it to be pickup up by the check for NULL though.

 

All the best

 

Keith

[newphpcoder]

So, what should i need to revise in my code?

 

Thank you

[newphpcoder]

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

[Pikachu2000]

MySQL's DATE() function should help you.

 

SELECT field FROM table WHERE DATE(datetime_field) BETWEEN '2010-01-02' AND '2011-12-31'

[newphpcoder]

Thank you it works