why would you not just use datetime for your column type and store your data correctly?
I found a solution to the problem. Thanks for taking the time to read my post.
I didn't ( and still don't ) want to use the datetime data type because the entire script is based on a very complex php calendar that does some very complex things very well.
Part of how it works is it creates those strings as part of the way it creates html element id's, therefore determining how ajax displays precise calendar information.
This complex, but very important script is not compatible with the datetime data format. It would be inefficient to change a very complex and helpful script simply to make it compatible with a smaller, simpler script that does much less.
Its more efficient to change the simpler script to make it compatible with the more complex.
I found a way to do it. The string I displayed earlier in this thread is created by concatenating several php variables together. I will simply add a new section to the script, and within that new section separate the variables so each one is its own string, and insert each into its own column in a new table.
That will separate out all the pieces of that string, in one specific place within the for loop, so I can write queries to search that table. The rest of this very complex script does not have to do that type of search. It can stay the way it is.
The script is too long and complex to post here or explain, but this is the solution I was looking for, and the answers I received in this thread helped a lot.
It is quite possible that storing the value as datatime will be to your advantage. . .
When the "php format" is needed re-arrange the value to this format in the select.
For every other use, the datetime would work quite well -without having the added complexity in all of the code to parse out the date. For ordering things or comparing by date, the datetime should be much easier to use than the non-standard format.
I think you are saying find a way to use the datetime datatype because it makes everything easier.
My intent was not so much that it will be "easier to code" but that i believe it will be easier to understand when someone needs to maintain this in the future when it is no longer "fresh" in people's minds.
If this column is ever to be used in reporting or driving some process (i.e.. ordering for some select comparing against some other date in a different column or row), using datetime (imho) will be much more efficient and easier to maintain.
What are the possible year range(from year and to year) in the column?
At that time in my mind was if year range was limited to 2000-2099(or may be 2000-2199),
ambiguity might be removed by finding the position of '20' was 5 or 6.
Aug1220211AM is Aug 12 2021 1AM and not Aug 1 2202 11AM
Aug2202111AM is Aug 2 2021 11AM and not Aug 22 0211 1AM
Aug3120211AM is Aug 31 2021 1AM and not Aug 3 1202 11AM
Aug1221211AM is Aug 12 2121 1AM and not Aug 1 2212 11AM
Aug2212111AM is Aug 2 2121 11AM and not Aug 22 1211 1AM
Last edited by tonkuma; 07-03-12 at 16:39.
Reason: Add another data "Aug3120211AM".