awk command to convert date format in a file

Question

Given below is the file content and the awk command used:

Input file:in_t.txt

1,ABC,SSS,20-OCT-16,4,1,0,5,0,0,0,0
2,DEF,AAA,20-JUL-16,4,1,0,5,0,0,0,0

Expected outfile:

SSS|2016-10-20,5
AAA|2016-07-20,5

I tried the below command:

awk -F , '{print $3"|"$(date -d 4)","$8}' in_t.txt

Got the outfile as:

SSS|20-OCT-16,5
AAA|20-JUL-16,5

Only thing I want to know is on how to format the date with the same awk command. Tried with

awk -F , '{print $3"|"$(date -d 4)","$8 +%Y-%m-%d}' in_t.txt

Getting syntax error. Can I please get some help on this?

Answer 1

Better to do this in shell itself and use date -d to convert the date format:

#!/bin/bash

while IFS=',' read -ra arr; do
   printf "%s|%s,%s\n" "${arr[2]}" $(date -d "${arr[3]}" '+%Y-%m-%d') "${arr[7]}"
done < file

SSS|2016-10-20,5
AAA|2016-07-20,5