Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    712
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Issue in saving 0000-00-00 00:00:00 or IsNull values

    Hi,
    I notice that in my query:

    Code:
    $result = mysql_query("INSERT INTO payroll.reg_att(EMP_NO, LOGIN, LOGOUT, TotalHours) 
    SELECT DISTINCT a.EMP_NO, a.LOGIN, a.LOGOUT, TIMEDIFF(a.LOGOUT, a.LOGIN)  FROM payroll.date_upload d, attendance.employee_attendance a JOIN hris.employment em ON (a.EMP_NO = em.EMP_NO AND em.STATUS = 'Reg Operatives') WHERE DATE(LOGOUT) BETWEEN '$FROM_DATE' AND '$TO_DATE' AND d.EMP_STATUS = 'Reg Operatives' OR ISNULL(LOGIN) OR ISNULL(LOGOUT)")  
     or die(mysql_error());
    it did not insert data where LOGOUT is 0000-00-00 00:00:00


    Thank you

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,480
    Thanks
    0
    Thanked 635 Times in 625 Posts
    How is the table defined?
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    712
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by felgall View Post
    How is the table defined?
    What do you mean table defined?

    Thank you

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Well, DOH! What a HUGE surprise that it didn't insert data for LOGOUT of 0000-00-00.

    LOOK at your SQL:
    Code:
    SELECT ... a.LOGOUT ...
    FROM payroll.date_upload d, attendance.employee_attendance a ...
    WHERE DATE(LOGOUT) BETWEEN '$FROM_DATE' AND '$TO_DATE'
    I doubt very very seriously that 0000-00-00 is BETWEEN those two dates!

    How about reading your own queries???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Aug 2010
    Posts
    712
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Well, DOH! What a HUGE surprise that it didn't insert data for LOGOUT of 0000-00-00.

    LOOK at your SQL:
    Code:
    SELECT ... a.LOGOUT ...
    FROM payroll.date_upload d, attendance.employee_attendance a ...
    WHERE DATE(LOGOUT) BETWEEN '$FROM_DATE' AND '$TO_DATE'
    I doubt very very seriously that 0000-00-00 is BETWEEN those two dates!

    How about reading your own queries???
    Yes, because LOGOUT is DATETIME, but I need to get DATE(LOGOUT) BETWEEN '$FROM_DATE' AND '$TO_DATE', now how can I get date even it is 0000-00-00

    thank you

  • #6
    Regular Coder
    Join Date
    Aug 2010
    Posts
    712
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Now, I realize why the LOGOUT = 0000-00-00 00:00:00 was not save because of this condition:

    Code:
    $FROM_DATE = 2012-03-01
    $TO_DATE = 2012-03-15
    WHERE DATE(LOGOUT) BETWEEN '$FROM_DATE' AND '$TO_DATE'
    now how can I get even the DATE(LOGOUT) = 0000-00-00

    Thank you so much

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    By not using BETWEEN.

    The date is either BETWEEN those two values or it is not. If it is not, then you can't use BETWEEN.

    It's the same thing as asking "How can 0 be between 7 and 13?"
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Regular Coder
    Join Date
    Aug 2010
    Posts
    712
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    By not using BETWEEN.

    The date is either BETWEEN those two values or it is not. If it is not, then you can't use BETWEEN.

    It's the same thing as asking "How can 0 be between 7 and 13?"
    I solve by adding
    Code:
    OR DATE(LOGOUT) = '0000-00-00'
    Thank you

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    BUT THAT MAKES NO SENSE!!!!

    If LOGOUT is '0000-00-00' and LOGIN is '2012-04-24' then your TIMEDIFF() value will be UTTER NONSENSE!!!

    Look here:
    http://dev.mysql.com/doc/refman/5.1/en/time.html

    So that means your TIMEDIFF() result will probably be '-838:59:59'

    If your incoming data is so screwed up that sometimes it has a date and sometimes it doesn't, then I think your entire operation needs to be torn down and replaced.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (04-25-2012)


  •  

    Posting Permissions

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