str_to_date issues

[bubblegum.anarchy]

The str_to_date function is driving me nuts.. working one minute and not working the next (returns null when not supposed to).

 

I have a simple public_holiday table with an `occurs` field of type varchar(255) not null default '1st January';

 

I have tried all manner of string data types and even setting the field to allow null but the function remains sporatic

 

the query used is:

SELECT str_to_date(occurs, '%D %M') FROM public_holiday; #works one day and returns null the next

SELECT str_to_date('1st January', '%D %M') from public_holiday; #always works

 

table contains:

1st January

26th January

Second Monday March

 

Before I give up on the function completely, does anyone know what might be causing the null returns?

[fenway]

Well, the 3rd record if your table doesn't match... why do I feel like I've seen this thread before?

[bubblegum.anarchy]

This might look similar to a recent thread where I requested a query to translate `Second Monday March`...

 

I expect `Second Monday March` to return null but I do not expect the same result for the 1st Jan and 26th Jan... anyways.. I have totally flagged the str_to_date function... now the values are being converted in php first since there are not many public holiday dates and I can apply the array in static form to a query using IN ().

[fenway]

So you're happy with this "solution"?

[bubblegum.anarchy]

you can certainly lock this thread but no solution... just alternate path.