tags:

views:

16

answers:

1

A MySQL table EMPLOYEE has columns (beginyear, beginmonth, beginday, empid) all of type int.

What's a correct query to return all rows that are equal to or greater than the date 2009/8/13? That's year, month, day.

A query such as this is incorrect because it wouldn't return rows that contained dates such as 2009/9/1 (filtered out by beginday >=13 in where clause below) or 2010/1/14.

SELECT *
FROM EMPLOYEE
where beginyear >= 2009
  and beginmonth >= 8
  and beginday >=13

Assume I can't make any changes to the schema and that I have to create some sort of query from JDBC to get the results.

+1  A: 

The best I could do with your bad situation of three different fields:

select *, concat(beginyear, '-',beginmonth,'-',beingday) as full_date 
  FROM TABLE 
   WHERE CONCAT(beginyear, '-',beginmonth,'-',beingday) >= '2009-08-13'

MySql's notion of a datetime expression is sort of peculiar, you might want to wrap the concat with a date() function to normalize it.

anq
+1: My thoughts too, but I'd go one further to encapsulate the `CONCAT` in `STR_TO_DATE(concat(beginyear, '-',beginmonth,'-',beingday), '%Y-%m-%d')` - because date formats aren't consistent. What sucks is that indexes on the three columns can't be used...
OMG Ponies
I ran the query "select *, concat(beginyear, '-',beginmonth,'-',beginday) as full_date from employee where full_date >= '2009-08-13'" and I received "Error code 1054, SQL state 42S22: Unknown column 'full_date' in 'where clause'"
Eunice
@Eunice: See update - can't reference a column alias in the WHERE clause
OMG Ponies
OMG Ponies: That worked. Thanks.Anq: thanks!
Eunice
thanks for editing my sloppy work, OMG Ponies -- glad to hear it all worked out, Eunice
anq