List:General Discussion« Previous MessageNext Message »
From:Gilles MISSONNIER Date:January 11 2007 10:47am
Subject:Re: how to take advantage of STR_TO_DATE
View as plain text  
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" <gimi@stripped>
> To: <mysql@stripped>
> 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 - gimi@stripped
01 44 32 81 36
Thread
how to take advantage of STR_TO_DATEGilles MISSONNIER10 Jan
  • Re: how to take advantage of STR_TO_DATEViSolve DB Team11 Jan
    • Re: how to take advantage of STR_TO_DATEGilles MISSONNIER11 Jan
      • Re: how to take advantage of STR_TO_DATEJake Peavy11 Jan
        • Re: how to take advantage of STR_TO_DATEDan Nelson11 Jan
          • Re: how to take advantage of STR_TO_DATEJake Peavy11 Jan
  • Re: how to take advantage of STR_TO_DATEFelix Geerinckx11 Jan