KarthikK has asked for the wisdom of the Perl Monks concerning the following question:

Hello all,
i have a datetime field in my database. when i get the value in my perl script i get it as "dd.mm.yyyy hh:mm". I want to convert this to the ISO format of "YYYY-MM-DD". Also when i run this script from a windows system whose regiaon settings points to "English (USA)" i get the value of this date field as "MM/DD/YYYY HH:MM"

Is it possible to have a conversion fn in perl to convert the date to ISO format irrespective of users's local settings?

I dont want to use any external PERL modules. Is this possible with standard modules?

Thx
Karthik

Replies are listed 'Best First'.
Re: Question on date formatting
by moritz (Cardinal) on Jun 19, 2008 at 10:08 UTC
    Most databases offer you a function to retrieve a date in a specific format, independently of locale settings.

    Instead of trying to parse all possible output formats, use that function instead.

    (IIRC Oracle lets you control the format with an environment variable - that's another direction worth exploring. But it's all futile speculation unless you tell us what DB system you're using).

[reply]
      Thx for ure feedback. I am using API's from IBM Rational ClearQuest and cannot have SQL tricks in my case
[reply]
Re: Question on date formatting
by bcrowell2 (Friar) on Jun 19, 2008 at 16:24 UTC 
    my $date = "01.01.1999 hh:mm";
$date =~ /\A(\d\d)\.(\d\d)\.(\d\d\d\d)/;
my $iso = "$3-$2-$1";

    [download]
[reply]
[d/l]
Re: Question on date formatting
by graff (Chancellor) on Jun 20, 2008 at 01:53 UTC
    If the differences among machines/environments consistently fall into just those two possibilities (dd.mm.yyyy versus mm/dd/yyyy), then the following should do what you want: 
    s{
   (\d{1,2}) ([./])
   (\d{1,2}) \2
   (\d{4}) \s+ (\d{1,2}):(\d{2})
 }
 {
   my @a=($2 eq ".") ? ($4,$3,$1) : ($4,$1,$3);
   sprintf( "%s-%02d-%02d %02d:%s", @a,$5,$6 )
 }ex;

    [download]
    The re-ordering of the first three fields depends on whether the field separators are periods or slashes, and the sprintf for the output format makes sure to include leading zeroes when necessary.

    (updated to try improving legibility of the code -- not sure I succeeded)

[reply]
[d/l]
      Thx..but how do i get your code working?
[reply]
        Example: 
        my @data = ( "blah blah 6/20/2000 4:56 foo bar baz\n",
             "bling blang 20.6.2000 4:56 flzb gzrk\n" );

for my $string ( @data ) {
    $string =~ s{ (\d{1,2}) ([./])
                  (\d{1,2}) \2
                  (\d{4}) \s+ (\d{1,2}):(\d{2})
                }
                {
                  my @a=($2 eq ".") ? ($4,$3,$1) : ($4,$1,$3);
                  sprintf( "%s-%02d-%02d %02d:%s", @a,$5,$6 )
                }ex;

    print $string;
}

        [download]
        That is, whatever string variable happens to contain one of the two types of date format, apply the lengthy "s{}{}" substitution statement to that string.

        UPDATE: Forgot to mention: if you have a string with more than one date in it (e.g. "from 1/2/2000 to 3/4/2000"), just change the "ex" at the end to "exg".

[reply]
[d/l]

Back to Seekers of Perl Wisdom