in reply to Convert date format in database table.

Why wait to fix the date? Munging 500000 text records before inserting should be quicker than doing 500000 updates after inserting. And 500000 really isn't that much. One possible fix would be something like (untested):
s#,"(\d+)/(\d+)/(\d+)\s+(\d+):(\d+)",#
 sprintf("%04d-%02d-%02d %02d:%02d:00", $3, $1, $2, $4, $5)#ex;

[download]

Replies are listed 'Best First'.
Re: Re: Convert date format in database table.
by cLive ;-) (Prior) on Oct 07, 2003 at 06:13 UTC
    Why use a regex when split will do? :) 
    my $date = '2/3/1906';
my $new_date = sprintf("%04d-%02d-%02d 00:00:00",(split /\//, $date)[2
+,1,0]);

# $new_date is "1906-03-02 00:00:00"

    [download]

    cLive ;-)

    Update: As tachyon points out, I misread the question - the phrase "must be all zeroes" threw me :)

    But again, I still say split :) 

    my $new_date = sprintf("%04d-%02d-%02d %02d:%02d:00",(split /\D+/, $da
+te)[2,1,0,3,4]);

    [download]

    :)

[reply]
[d/l]
[select]

      So you deal with the HH:MM (which would require a split/[:/ ]/, and don't have to parse the CSV of which this date string was stated to be a field.....

      cheers

      tachyon

      s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

[reply]
[d/l]
        Re-reading the question :)

        'CCYY-MM-DD hh:mm:ss' where all zeroes are required

        Ah, OK, let's re-read that so it makes sense - "where leading zeroes are required". LOL.

        Touché

        cLive ;-)

[reply]
      Why use a regex when split will do?
      Because a regex allows for more precise format matching (year can be '\d{4}', the rest can be '\d{1,2}) in case, e.g., we have badly formatted dates and we don't want errors upon inserting to the database. I know, it's not necessarily a great reason, but it's a reason :-)
[reply]

In Section Seekers of Perl Wisdom