SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    test it by inserting a row of literal values...
    Code:
    INSERT INTO payroll.reg_att(EMP_NO, LOGIN, LOGOUT, TotalHours)
    VALUES ( 11111, '2012-02-29' , '0000-00-00' , 937 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    test it by inserting a row of literal values...
    Code:
    INSERT INTO payroll.reg_att(EMP_NO, LOGIN, LOGOUT, TotalHours)
    VALUES ( 11111, '2012-02-29' , '0000-00-00' , 937 )
    LOGIN and LOGOUT is DATETIME type. My concern is why in LOGIN it saves 0000-00-00 00:00:00 but in LOGOUT not

    Thank you

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    perhaps you could do a SHOW CREATE TABLE for further investigation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is the result of:

    SHOW CREATE TABLE reg_att;

    CREATE TABLE `reg_att` (
    `REF_ID` int(11) NOT NULL AUTO_INCREMENT,
    `EMP_NO` varchar(20) NOT NULL,
    `LOGIN` datetime DEFAULT NULL,
    `LOGOUT` datetime DEFAULT NULL,
    `TotalHours` time DEFAULT NULL,
    `Rendered` time DEFAULT NULL,
    `Late` time DEFAULT NULL,
    `Exceed_Hour` time DEFAULT NULL,
    PRIMARY KEY (`REF_ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=113833 DEFAULT CHARSET=latin1

    Thank you

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i tested your CREATE TABLE statement and it created the table successfully

    i added three rows:
    Code:
    INSERT 
      INTO reg_att
    VALUES 
     ( 12345, 'empl#1', '2012-04-04 09:37', '2012-04-04 21:55', '12:18', '12:00', '00:18', '00:00' )
    ,( 56789, 'empl#2', '0000-00-00 00:00', '2012-04-04 21:55', '12:18', '12:00', '00:18', '00:00' )
    ,( 33333, 'empl#3', '2012-04-04 09:37', '0000-00-00 00:00', '12:18', '12:00', '00:18', '00:00' )
    ;
    then i retrieved them:
    Code:
    SELECT * 
      FROM reg_att;
    everything is as expected

    therefore, you must be doing something else

    because there is no problem inserting a value of '0000-00-00 00:00' into either the LOGIN or LOGOUT column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something what?

    Thank you

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    something else
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i don't do php but that doesn't look valid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I solve by adding
    Code:
    OR DATE(LOGOUT) = '0000-00-00'
    Thank you


Bookmarks

Posting Permissions

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