Menu
I am currently developing an application that displays documents and allows the members to search for these documents by a number of different parameters, one of them being date range.
The problem I am having is that the database schema was not developed by myself and the creator of the database has created a 'date' table with fields for 'day','month','year'.
I would like to know how I can select a specific day, month, year from the table and create a date object in SQL so that I can compare dates input by the user using BETWEEN.
Below is the structure of the date table:
CREATE TABLE IF NOT EXISTS `date` ( `deposition_id` varchar(11) NOT NULL default '', `day` int(2) default NULL, `month` int(2) default NULL, `year` int(4) default NULL, PRIMARY KEY (`deposition_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
612
First, create a table named people with birth date column with DATE data type. Next, insert a row into the people table. Then, query the data from the people table. After that, use the two-digit year format to insert data into the people table.
In this tutorial, we will learn about the MySQL DATE_FORMAT() function. The date value in MySQL is in the format “YYYY-MM-DD”. However, such a date value may be difficult to read for some people – who prefer it in some other format like DD/MM/YYYY or MM/DD/YYYY. Some people may prefer to see the day name value as well.
You can use now() with default auto fill and current date and time for this. Later, you can extract the date part using date() function. Let us set the default value with some date.
Change the curdate() (current date) format in MySQL The current date format is 'YYYY-mm-dd'. To change current date format, you can use date_format().
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'; You can use STR_TO_DATE() function.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
