MySQL date format

by

Go to MySQL Reference – 10.5. Data Type Storage Requirements

Search for: Storage Requirements for Date and Time Types

Dates are internally stored as A three-byte integer packed as DD + MM×32 + YYYY×16×32

But, if you select a date column for display, it has to be shown in some way, so it comes out as 0000-00-00. It is not stored as a char(10) with that specific format.

If, for display, you need to see a specific format, you can convert it to VARCHAR in a specific format using Date_Format(). However, bear in mind that if you are using the query in a programming tool, this is not what you want to do. You want the raw date value, for display purposes, there will be a similar formatting function from whatever programming environment you use.

As you can see from the reference in DATE_FORMAT, you will want to use '%d-%m-%Y', e.g.

SELECT col1, col2, DATE_FORMAT(datecolumn, '%d-%m-%Y') AS datecolumn, more1...
FROM sometable
....

More Related Contents:

  1. Left Outer Join doesn’t return all rows from my left table?
  2. Get a list of dates between two dates
  3. How to populate a table with a range of dates?
  4. MySQL Query GROUP BY day / month / year
  5. Error in MySQL when setting default value for DATE or DATETIME
  6. How to get the number of days of difference between two dates on MySQL?
  7. Mysql: Select all data between two dates
  8. Difference between two dates in MySQL
  9. How to get difference between two dates in months using MySQL query?
  10. Select mysql query between date?
  11. How do I select between the 1st day of the current month and current day in MySQL?
  12. Get the latest date from grouped MySQL data
  13. How to get an age from a D.O.B field in MySQL?
  14. mysql: get record count between two date-time
  15. How to get first day of every corresponding month in mysql?
  16. Count days between two dates, excluding weekends (MySQL only)
  17. How to cast DATETIME as a DATE in mysql?
  18. How do I get the first day of the week of a date in mysql?
  19. MySQL: How to select records for this week?
  20. Compare only day and month with date field in mysql
  21. MySQL – select data from database between two dates
  22. How to select only date from a DATETIME field in MySQL?
  23. Select all months within given date span, including the ones with 0 values
  24. Update date + one year in mysql
  25. Insert/ Update random date in MySQL
  26. mysql date comparison with date_format
  27. How to compare timestamp dates with date-only parameter in MySQL?
  28. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  29. How do I extract Month and Year in a MySQL date and compare them?
  30. Working with INTERVAL and CURDATE in MySQL

Leave a Comment