Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Re^3: Date format

by Corion (Patriarch)
on Sep 06, 2018 at 08:52 UTC ( [id://1221847]=note: print w/replies, xml ) Need Help??


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;

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++; }

    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

      You already have the interesting parts to change code:

      # my $date = $arr_ref->$datecol; # $date =~ s/ //;

      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 }

      ... 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;

      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.

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

      select to_char(sysdate,'fmDD.MMfm.YYYY') from dual
        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++; }

        Thanks,

        JP

        2018-09-08 Athanasius added code tags

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1221847]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others lurking in the Monastery: (6)
As of 2024-03-28 15:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found