Why DAY(some_column) truncate lead zero ?

[gremolin]

hi all.
I have a problem with a date extraction. In a table I have a date column.
I want to take the rows in which the value of this date column is after, for example, 1 December, no matter of year.
Like EXTRACT(YEAR_MONTH FROM some_column) function do, I concatenate like this
select * from some_table where CONCAT('', MONTH(some_column), DAYOFMONTH(some_column)) > 1201;
But if I have a date like '2002-12-06' CONCAT return this --> 126 and this row is not in results. I note that I don't want to remove lead zero from the day of my date.
Can you advice me what to do.
thanks.

 

[sleipnir214]

try this:

where date_format(some_column, '%m%d') > '1201'

date_format() is a little more straightforward than the concat() you're using.

Since you're function outputs a string, you should compare it to a string, not an integer Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[swampBoogie]

so you want 1206 instead?

select concat(right(concat('0',extract(month from date)),2),
right(concat('0',extract(day from date)),2)) from t

That will retain leading zero for both month and day.

 

[gremolin]

thanks, I resolve my problem.
DATE_FORMAT() is more powerfull from CONCAT()