Create date from day, month, year fields in MySQL

by

When you have integer values for year, month and day you can make a DATETIME by combining MAKEDATE() and DATE_ADD(). MAKEDATE() with a constant day of 1 will give you a DATETIME for the first day of the given year, and then you can add to it the month and day with DATE_ADD():

mysql> SELECT MAKEDATE(2013, 1);
+-------------------+
| MAKEDATE(2013, 1) |
+-------------------+
| 2013-01-01        |
+-------------------+

mysql> SELECT DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH);
+---------------------------------------------------+
| DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH) |
+---------------------------------------------------+
| 2013-03-01                                        |
+---------------------------------------------------+

mysql> SELECT DATE_ADD(DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH), INTERVAL (11)-1 DAY);
| DATE_ADD(DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH), INTERVAL (11)-1 DAY) |
+----------------------------------------------------------------------------------+
| 2013-03-11                                                                       |
+----------------------------------------------------------------------------------+

So to answer the OP’s question:

SELECT * FROM `date`
WHERE DATE_ADD(DATE_ADD(MAKEDATE(year, 1), INTERVAL (month)-1 MONTH), INTERVAL (day)-1 DAY)
BETWEEN '2013-01-01' AND '2014-01-01';

More Related Contents:

  1. MySQL query finding values in a comma separated string
  2. In MySQL queries, why use join instead of where?
  3. generate days from date range
  4. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  5. Can table columns with a Foreign Key be NULL?
  6. MySQL Query to select data from last week?
  7. MySQL not using indexes with WHERE IN clause?
  8. How Can I use “Date” Datatype in sql server?
  9. MySQL dump by query
  10. single fixed table with multiple columns vs flexible abstract tables
  11. MySQL’s now() +1 day
  12. How many rows in a database are TOO MANY?
  13. SQLite – ORDER BY RAND()
  14. SQL – How to transpose?
  15. Which SQL query is better, MATCH AGAINST or LIKE?
  16. MySQL equivalent of DECODE function in Oracle
  17. How to delete Duplicates in MySQL table
  18. Best database field type for a URL
  19. Query that ignore the spaces
  20. How to Optimize Queries in a Database – The Basics
  21. MySQL – how to front pad zip code with “0”?
  22. Find rows that have the same value on a column in MySQL
  23. what is the difference between GROUP BY and ORDER BY in sql
  24. Mysql strip time component from datetime
  25. How to change string date to MySQL date format at time of import of CSV using MySQL’s LOAD DATA LOCAL INFILE
  26. How do I list all the columns in a table?
  27. Two single-column indexes vs one two-column index in MySQL?
  28. VARCHAR as foreign key/primary key in database good or bad?
  29. How to find all IDs of children recursively?
  30. Doing a WHERE .. IN subquery in Doctrine 2

Leave a Comment