[opensuse] Mysql question
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? Thanks, JIM -- Jim Hatridge Linux User #88484 Ebay ID: WartHogBulletin ------------------------------------------------------ WartHog Bulletin Info about new German Stamps http://www.WartHogBulletin.de Many Enemies -- Much Honor! Anti-US Propaganda stamp collection http://www.manyenemies-muchhonor.info An American in Bavaria http://www.gaubodengalerie.de -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
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 don't EVER use MySQL; but with most databases this is just a matter of changing your locale. Should be doable via an environment variable (client side) or a parameter in your DSN/connection-string. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On Wed, April 11, 2007 17:54, Adam Tauno Williams wrote:
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 don't EVER use MySQL;
Please don't make a statement like that without explaining why. I can think of a few reasons, but I don't know everything and I'm sure more people on this list would now why. Hans -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
there are plenty of functions to work with DATE in mysql, check this:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
~ig
On 4/11/07, Hans du Plooy
On Wed, April 11, 2007 17:54, Adam Tauno Williams wrote:
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 don't EVER use MySQL;
Please don't make a statement like that without explaining why. I can think of a few reasons, but I don't know everything and I'm sure more people on this list would now why.
Hans
-- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
-- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On Wed, 2007-04-11 at 18:05 +0100, Hans du Plooy wrote:
On Wed, April 11, 2007 17:54, Adam Tauno Williams wrote:
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 don't EVER use MySQL; Please don't make a statement like that without explaining why.
This list is not the appropriate place to debate the relative merits of various RDBMS. This preface was to indicate that this is not a suggestion from specific experience. But the locale is always the first thing I would check on any RDBMS regarding something like date format - it is what *SHOULD* be the controlling factor.
I can think of a few reasons, but I don't know everything and I'm sure more people on this list would now why.
-- -- Adam Tauno Williams Network & Systems Administrator Consultant - http://www.whitemiceconsulting.com Developer - http://www.opengroupware.org -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On Wednesday 11 April 2007 19:05, Hans du Plooy wrote:
On Wed, April 11, 2007 17:54, Adam Tauno Williams wrote:
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 don't EVER use MySQL; but with most databases this is just a matter of changing your locale. Should be doable via an environment variable (client side) or a parameter in your DSN/connection-string.
I don't think this is what I want to do. I only want to change it on one database. The others are fine. If I can't change mysql, is there a way to reverse the date in the file before I put it in mysql?
Please don't make a statement like that without explaining why. I can think of a few reasons, but I don't know everything and I'm sure more people on this list would now why.
Hans
Personally I use mysql because many many years ago I learned Dbase 3+, when I changed to Linux I found that mysql was almost the same as Dbase. So it was easy to learn. There might be better databases, but mysql is fine for every thing I do. Thanks, JIM -- Jim Hatridge Linux User #88484 Ebay ID: WartHogBulletin ------------------------------------------------------ WartHog Bulletin Info about new German Stamps http://www.WartHogBulletin.de Many Enemies -- Much Honor! Anti-US Propaganda stamp collection http://www.manyenemies-muchhonor.info An American in Bavaria http://www.gaubodengalerie.de -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On Wednesday 11 April 2007 09:42:36 am 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?
Um, my suggestion would be to do some kind of CAST or CONVERT. I'm not so familiar with MySQL in terms of raw Transact-SQL, but in most cases you can do this. Given a text file (that is your source, right?) you can add as part of your INSERT statement something like a part of the string so you can piece it together. I don't know if you're using a program (C, PHP) or just SQL. In SQL, you would need to perform a lot of manipulation. You'd be better using Kate (or Vi) to parse the file yourself. -- kai Free Compean and Ramos http://www.grassfire.org/142/petition.asp http://www.perfectreign.com/?q=node/46 -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
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
HI Randall et al.. This was what I was looking for! THANKS ALL! JIM On Wednesday 11 April 2007 22:04, Randall R Schulz wrote:
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
-- Jim Hatridge Linux User #88484 Ebay ID: WartHogBulletin ------------------------------------------------------ WartHog Bulletin Info about new German Stamps http://www.WartHogBulletin.de Many Enemies -- Much Honor! Anti-US Propaganda stamp collection http://www.manyenemies-muchhonor.info An American in Bavaria http://www.gaubodengalerie.de -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
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?
Thanks,
JIM
I believe MySQL run there own mailing lists to deal with MySQL issues, I would suggest going to the relevant MySQL website (http://dev.mysql.com ) and ask this question there.
participants (7)
-
Adam Tauno Williams -
G.T.Smith -
Hans du Plooy -
I.B. -
James Hatridge -
Kai Ponte -
Randall R Schulz