0

i have 2 dates datetime column in my database and the format is YYYY-mm-dd H:i:s when 0000-00-00 00:00:00 this values gets insert into db and when i convert date into different time format that's give me something 12 value and time diff function of mysql is also give wrong value. look at this picture enter image description here

this is my mysql query

SELECT 
    DATE_FORMAT(EAR.CALENDAR_DATE, '%d-%b-%Y, %a') AS FORMATTED_CALENDAR_DATE,
    DATE_FORMAT(EAR.IN_ATTENDANCE_DATE_TIME, '%h:%i') AS IN_TIME,
    DATE_FORMAT(EAR.OUT_ATTENDANCE_DATE_TIME, '%h:%i') AS OUT_TIME,
    DATE_FORMAT(TIMEDIFF(EAR.OUT_ATTENDANCE_DATE_TIME,
                    EAR.IN_ATTENDANCE_DATE_TIME),
            '%h:%i') AS TIME_DIFF,
            IN_ATTENDANCE_DATE_TIME,
            OUT_ATTENDANCE_DATE_TIME,
    (SELECT 
            CONCAT(ED.F_NAME, ' ', ED.L_NAME)
        FROM
            EMPLOYEE_DETAILS ED
        WHERE
            EAR.EMP_ID = ED.ID
        LIMIT 1) AS FULL_NAME
FROM
  EMPLOYEE_ATTENDANCE_RECORD EAR
WHERE
    MONTH(CALENDAR_DATE) = MONTH(CURDATE())
        AND YEAR(CALENDAR_DATE) = YEAR(CURDATE())
        AND EAR.EMP_ID = 1
ORDER BY CALENDAR_DATE , EMP_ID ASC;

closed as unclear what you're asking by mustaccio, Marcello Miorelli, Laurenz Albe, hot2use, Michael Green Sep 11 at 12:19

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Change the sql_mode to disallow inserting a "zero" date. – Rick James Sep 10 at 15:19
  • no, if no action taken then zero should insert. – sayyed tabrez Sep 11 at 13:01