DATE comparison issue 1

[PaulFynch]

Hello wonderful TekTip community,

I am stumped on this one, I need your help:

I have a field storing a string value of a year/month like this : 200802 in a field called 'display_month' that I need to compare to NOW()...when I run this query:

SELECT display_month,
YEAR(STR_TO_DATE(wd_orders_meta.display_month, '%Y%m')) AS display_year,
YEAR(NOW()) as now_year
FROM my_table
WHERE DATE_FORMAT(STR_TO_DATE(display_month, '%Y%m'), '%Y%m') = DATE_FORMAT(NOW(), '%Y%m')

My query runs and tells returns a record that has a display_month value of 200802 (next feb) essentially saying that 200802 = 200702. It doesn't do any other month, it just seems confused about the year. I tried using YEAR() for the comparison, but it still returns 2008 = 2007. I tried casting both values as DATE() in the comparison to no avail. I tried CAST() and convert with only errors in the comparison.

I'm stumped...anyone have any thoughts?
Thanks
Paul

"If you build it....it won't work the first time.....or the second

 

[PaulFynch]

Hey again,

I just checked the DB, the 'display_month' field is actually mediumint(9), if that makes any difference.

Paul

"If you build it....it won't work the first time.....or the second

 

[r937]

... where floor(display_month/10) = year(current_date)

r937.com | rudy.ca

 

[r937]

ooops, that should be /100, not /10

r937.com | rudy.ca

 

[PaulFynch]

I'll give that a try r937 and let you know how it works.

pf

"If you build it....it won't work the first time.....or the second

 

[PaulFynch]

I found it r937, it had nothing to do with the date math (man I spent a couple hours on this one), a misplaced 'OR' (or rather, 'unbracketed') had my query accepting conditions that weren't relevant to the date...

Star for the effort...

Thanks,
Paul

"If you build it....it won't work the first time.....or the second