Date format YYYY/MM/DD to DD/MM/YYYY

Shell Programming and Scripting
1

I am getting output of YYYY-MM-DD and want to change this to DD/MM/YYYY.
When am running the query in 'Todd'

to_date(column_name,'DD/MM/YYYY')

am getting the required o/p of DD/MM/YYYY, But when am executing the same query(Netezza) in linux server(bash) am getting the output of YYYY-MM-DD

file

SAP|2017-03-23
REEF-U|2017-03-24
FEB|2016-06-12
BEN-JEF|2017-09-23
TUJJI|2017-03-30

As of now am using multiple command to do this, is there a way to do this in single command?

awk -F"|" '{print $2}' file | awk -F"-" '{print $3"/"$2"/"$1}' >file2
awk -F"|" '{print $1}' file3 ; paste file3 file2

Required output:

SAP|23/03/2017
REEF-U|24/03/2017
FEB|12/06/2016
BEN-JEF|23/09/2017
TUJJI|30/03/2017
2

How about

awk  'BEGIN {FS = OFS = "|"} {split ($2, T, "-"); print $1, T[3] "/" T[2] "/" T[1]}' file
SAP|23/03/2017
REEF-U|24/03/2017
FEB|12/06/2016
BEN-JEF|23/09/2017
TUJJI|30/03/2017
1 Like
3

A Perl alternative:

perl -pe 's/(\d{4})-(\d{2})-(\d{2})/$3\/$2\/$1/' roozo.file

Output:

SAP|23/03/2017
REEF-U|24/03/2017
FEB|12/06/2016
BEN-JEF|23/09/2017
TUJJI|30/03/2017
1 Like
4

While you can always fix the date format using a scripting language after you get it out of the database, you may want to explore the possibility of getting it in the correct format from the database itself.

  • If your database column "column_name" is of datatype "DATE", then try the "to_char" function:
to_char(column_name,'DD/MM/YYYY')
  • If your database column "column_name" is of datatype "TIMESTAMP", then try something like this:
to_char(date(column_name),'DD/MM/YYYY')
1 Like