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 7 of 7
  1. #1
    New Coder
    Join Date
    Jun 2007
    Location
    Canada
    Posts
    49
    Thanks
    1
    Thanked 1 Time in 1 Post

    Handling empty datetimes

    When a datetime field in the DB is empty, it returns 0000-00-00 00:00:00. When I display this field I don't want anything to show. Other than doing an IF...then check for that pattern, is there a better way to convert this to a blank?


    Thanks
    Of all the things I've lost in my life time, my mind is the one I miss the most!

  2. #2
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,650
    Thanks
    2
    Thanked 406 Times in 398 Posts
    I can't think of a better way than IF.

  3. #3
    New Coder
    Join Date
    Jun 2007
    Location
    Canada
    Posts
    49
    Thanks
    1
    Thanked 1 Time in 1 Post
    OK... thanks

    I just wasn't sure if there was a built-in method or something else that I could use.
    Of all the things I've lost in my life time, my mind is the one I miss the most!

  4. #4
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,650
    Thanks
    2
    Thanked 406 Times in 398 Posts
    IF() is built-in. You may want to post this in, or ask it to be moved to, the MySQL forum.

  5. #5
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    You can also use IFNULL() to give it a more descriptive result such as 'None' or 'N/A'.

  6. #6
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,650
    Thanks
    2
    Thanked 406 Times in 398 Posts
    I was going to suggest that, but I tested it and MySQL doesn't seem to consider '0000-00-00 00:00:00' to be NULL, even if the field is datetime.

  7. #7
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    I have to admit I didn't test it. I was drawing on Oracle experience (where such a date would be NULL). I wonder why MySQL doesn't consider that NULL.


 

Posting Permissions

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