Converting DD MM YYYY into YYYY-MM-DD?

[Jonathan Gardner]

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.