in reply to Re^2: Date format
in thread Date format

So, what ideas did you try and how did they fail?

You can help us help you better by showing us the code you have tried and telling us how it failed for you. That allows us to help you much better clear up your misunderstandings.

For example, you could start with this program:

#!perl
use strict;
use warnings;
use Test::More;

my $input_value = '02/08/2018';
my $desired_value = '2/8/2018';

# do stuff with input value:
my $transformed_value = $input_value;
$transformed_value =~ s!0!!g; # remove all zeroes

is $transformed_value, $desired_value, "Conversion strips leading zero
+es";

done_testing;

[download]

Replies are listed 'Best First'.
Re^4: Date format
by jsuresh (Acolyte) on Sep 06, 2018 at 11:04 UTC
    Hi,

    I tried to change the format of the date while fetching it from the DB but, I'm unable to do so, it is oracle DB.

    # execute the sql query
         my $sth1 = $dbh->prepare($sql1);
         my $sth2 = $dbh->prepare($sql2);
         $sth1->execute();
         $sth2->execute();
# write the data into worksheet
         my $row1=1;

         while(my @field=$sth1->fetchrow_array){
                my $col1=0;
                $field[8]=$priority{$field[8]};
                $field[7]=$priority{$field[7]};
                $field[33]=$statusyn{$field[33]};
                $field[17]=$status{$field[17]};
                $field[14]=$statusyn{$field[14]};
                $field[44]=$tkttype{$field[44]};
                $field[32]=$statusyn{$field[32]};
                my $arr_ref=\@field;
               # my $date = $arr_ref->[$datecol];
               # $date =~ s/ //;
                $worksheet1->write_row($row1,0,$arr_ref);
                $row1++;
        }

    [download]

    The date is one of the column in my expoerted data. And it is of the format "06-05-2018" but I need it to be '6-5-2018'.

    How can I proceed to change the format to my desired format once after the code is fetched from DB?

    Thanks,

    JP

    2018-09-08 Athanasius added code tags

[reply]
[d/l]

      You already have the interesting parts to change code:

       # my $date = $arr_ref->$datecol;

# $date =~ s/ //;

      [download]

      I suggest that you take the database out of the problem and start out with a simple subroutine that converts the date to the format you want:

      sub fix_oracle_date {
    my( $date ) = @_;

    # $date =~ s/ //;

    return $date
}

      [download]

      ... and then test your subroutine using (for example) Test::More, which makes this very easy:

      use Test::More;

is fix_oracle_date('08/02/2018'), '8/2/2018', "Correct date for 8/2/20
+18";
is fix_oracle_date('08/03/2018'), '8/3/2018', "Correct date for 8/3/20
+18";
is fix_oracle_date('08/04/2018'), '8/4/2018', "Correct date for 8/4/20
+18";
is fix_oracle_date('08/13/2018'), '8/13/2018', "Correct date for 8/13/
+2018";

done_testing;

      [download]

      Now you have a good testbed and you can work on implementing fix_oracle_date until it returns the correct data for your input.

      Maybe you want to start with the implementation I gave you in my previous reply?

      See also How to ask better questions using Test::More and sample data, which outlines this process.

[reply]
[d/l]
[select]

      You don't show your query but you want something like this:

      select to_char(sysdate,'fmDD.MMfm.YYYY') from dual

      [download]
[reply]
[d/l]
        Hi,

        Yes, I have already tried this(FMdd/FMmm/FMyyyy), it was not coming. I tried using the regex which was mentioned in the above chat, now it is coming but the issue is/ :, the leading zero in the time is also need to be removed.

        Expected Output: 1/8/2018 5:58

        Getting Output:1/8/2018 05:58

        My code 

         while(my @field=$sth1->fetchrow_array){
                my $col1=0;
                $field[19]=~ s!^0!!g;
                print"\t $field[19]";
                $field[8]=$priority{$field[8]};
                $field[7]=$priority{$field[7]};
                $field[33]=$statusyn{$field[33]};
                $field[17]=$status{$field[17]};
                $field[14]=$statusyn{$field[14]};
                $field[44]=$tkttype{$field[44]};
                $field[32]=$statusyn{$field[32]};
               my $arr_ref=\@field;
              # my $date = $arr_ref->[$datecol];
               # $date =~ s/ //;
                $worksheet1->write_row($row1,0,$arr_ref);
                $row1++;
        }

        [download]

        Thanks,

        JP

        2018-09-08 Athanasius added code tags

[reply]
[d/l]

In Section Seekers of Perl Wisdom