hello,
I rewrite my question in a simpler (?) way :
How could I load in the database, data from a text file containaing date
in a NOT MySQL standard date format [precisely char(10)], so that I get
the date into a MySQL standard date format in a column of type "date" ?
an other way to ask my question :
how do I transform a text "15/10/1999" into a date "1999-10-15"
when I load data from a text file into a MySQL database ?
I know that I could use a script to rewrite the text "15/10/1999"
as text "1999-10-15", and then load the file into Mysql (mysql
will accept the "1999-10-15" as a date format). I think that
I might take advantage of STR_TO_DATE, but I dont' know how.
thanks.
==================================================
On Thu, 11 Jan 2007, ViSolve DB Team wrote:
Hi,
STR_TO_DATE() simply converts the given format string to datetime value. So
to change the format of the date dispaly, go for DATE_FORMAT().
For Instance,
mysql> select DATE_FORMAT('2007/10/01','%d/%m/%Y');
or
mysql> select DATE_FORMAT(datecolumn,'%d/%m/%Y') from table1;
Thanks
ViSolve DB Team
----- Original Message ----- From: "Gilles MISSONNIER" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, January 11, 2007 12:49 AM
Subject: how to take advantage of STR_TO_DATE
Hello the list
I have a bunch of data that I load in the base through
the "load data infile" procedure.
These data contain date with the following date format :
%d/%m/%Y [ that is day/month/year_4digit ]
I could rewrite the date with a script (perl, shell,)
to convert "day/month/year_4digit" into the standard MySQL format
that is "year_4digit-month-day", then load data in the base.
but I think I could take advantage of the STR_TO_DATE feature :
mysql> SELECT STR_TO_DATE('15/10/1999', '%d/%m/%Y');
+---------------------------------------+
| STR_TO_DATE('15/10/1999', '%d/%m/%Y') |
+---------------------------------------+
| 1999-10-15 |
+---------------------------------------+
I don't know how to do it on the fly :
should I create an string colum, in which I put the date like
"15/10/1999"
then run a mysql procedure that use STR_TO_DATE to fill a date column ?
how to do this ?
regards,
=========================================
_-¯-_-¯-_-¯-_-¯-_
Gilles Missonnier
IAP - [EMAIL PROTECTED]
01 44 32 81 36
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]