Converting DD MM YYYY into YYYY-MM-DD?

Jonathan Gardner jgardner at jonathangardner.net
Tue Aug 18 02:05:28 CEST 2009


On Aug 17, 3:26 pm, Gilles Ganault <nos... at nospam.com> wrote:
>         I need to convert DD MM YYYY dates into the MySQL-friendly
> YYYY-MM-DD, and translate the month name from literal French to its
> numeric equivalent (eg. "Janvier" into "01").
>
> Here's an example:
>
> SELECT dateinscription, dateconnexion FROM membres LIMIT 1;
> 26 Mai 2007|17 Août 2009 - 09h20
>
> I'd like to update the row into "2007-05-26" and "2009-08-17 09:20",
> respectively.
>
> What is the best way to do this in Python?
>

Unfortunately, there isn't any string to date parsers in the built-
ins. Not to worry, though, since writing your own is easy, especially
if you use regular expressions from the re module. I suggest using an
RE such as:

    r"(?P<date>\d+)\s+(?P<month>\w+)\s+(?P<year>\d+)"

If you want to translate month names to month numbers, then you need
some sort of dict to do so. Unfortunately, there isn't a terrific
standard for this, so your best bet is to put it in some file
somewhere, or even hard-code it in your code. (Month names won't
change over the lifetime of your program, so it's reasonable to put
them in your code somewhere.)

    month_names_to_numbers = {
        'jan':1, ... }

Once you have the year, month, and date, formatting it is trivial with
the built-in formatter.

    "%04d-%02d%02d %02d:%02d" % (year, month, date, hour, minute)

The variety of date formats out there have prevented a universal,
clean solution to this problem. Until we all start sticking to the
same conventions, we will always have to write code to translate dates
from one format to another.



More information about the Python-list mailing list