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.

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

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

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

the issue is/ :, the leading zero in the time is also need to be removed.

So why not account for that? You could use a negative lookbehind as in this SSCCE:

use strict;
use warnings;

use Test::More tests => 1;

my $datetime = '01/08/2018 05:58';
my $want = '1/8/2018 5:58';

my $have = $datetime;
$have =~ s/(?<![:\d])0//g;
is ($have, $want);
[download]

If you use <code> tags in your post your code will render better too.

"Yes, I have already tried this(FMdd/FMmm/FMyyyy), it was not coming."

Had you copied and paste the code I gave you I'm pretty sure it'd work on any Oracle database. You still don't show what SQL you are actually running, or how it fails, and FM doesn't work the way you assume it does. Think of it as a toggle, and read the documentation to understand what you're trying to use.

select to_char(sysdate - interval '4' hour, 'fmdd/mm/yyyy HHfm:mm') fr
+om dual

6/9/2018 8:09
[download]

I set the system date back a few hours to illustrate the format you say you want because of my timezone. Please read and understand How do I post a question effectively? & PerlMonks for the Absolute Beginner.