On Wednesday 11 April 2007 09:42, James Hatridge wrote:
Hi all,
I have records with the date in DD-MM-YYYY, but mysql wants them in YYYY-MM-DD. Is there anyway of changing Mysql's format?
I'm not 100% sure, but I checked a couple of references and it appears that while MySQL has a certain flexibility about input formats (and tremendous flexibility about outputting dates), those input formats are all in order (left to right) of descending magnitude: century, year, month, day, hour, minute second. So I'd just use sed on the raw text input you'll be reading before passing it to MySQL and be done with it. If your inputs are in a format as rigid and unvarying as that shown above, it's trivial to match and rearrange them with sed: % sed -r -e 's/(...)([0-9]{2})-([0-9]{2})-([0-9]{4})(...)/\1\4-\3-\2\5/' The (...) at either end is meant to represent any necessary constraints necessary to keep the ##-##-#### pattern from matching inappropriate portions of the input line. If there's no ambiguity with other content of those lines, the left one can be simply (.*) and the right one omitted. If there are multiple occurrences, the required pattern is a little more elaborate (but recall that parenthesized groups _do_ nest) and closure (?, * and {m} or {m,n}) operators can be applied to parenthesized sub-expressions. Note that this will not validate the dates, so it will happily operate on months greater than 12, days greater than 28, 30 or 31 and years far in the future or back in the dark ages.
Thanks,
JIM -- Jim Hatridge
Randall Schulz -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org