Question: 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;

```

Answer: 
To build a sortable date string from that, you'll need `CONCAT` to join the bits together and `LPAD` to make sure the month and day fields are two digits long. Something like this:

```
CONCAT(`year`,'-',LPAD(`month`,2,'00'),'-',LPAD(`day`,2,'00'))

```

Once you have that, you should be able to use `BETWEEN`, as they'll be in a sortable format. However if you still need to convert them to actual datetime fields, you can wrap the whole thing in `UNIX_TIMESTAMP()` to get a timestamp value.

So you'd end up with something like this:

```
SELECT UNIX_TIMESTAMP(CONCAT(`year`,'-',LPAD(`month`,2,'00'),'-',LPAD(`day`,2,'00'))) as u_date
WHERE u_date BETWEEN timestamp_1 and timestamp_2

```

However, be aware that this will be *massively* slower than if the field was just a simple timestamp in the first place. And you should definitely make sure you have an index on the year, month and day fields.
Try to use CONCAT() and make it one field and compare .

Am not sure you can compare it as date after concatenation.

You can compare as integer.

concatinate year month day and make an integer like this 20101017 and compare.

Hopefully :)