2

I am trying to dynamically convert one column in text file containing date in dd/mm/yyyy hh:mm:ss format to yy-mm-dd hh:mm:ss. Following command is working but giving output in yy-dd-mm and NOT yy-mm-dd as I need.

echo $(date -d "8/11/2018 06:20:57 AM" "+%y-%m-%d %H:%M:%S")

Output:

18-08-11 06:20:57

Moreover, this is not working for dates > 12 as it is treating first digit as month not date as below:

echo $(date -d "28/11/2018 06:20:57 AM" "+%y-%m-%d %H:%M:%S")

Output:

date: invalid date ‘28/11/2018 06:20:57 AM’

Is there any way to make it work?

Improve this question
3
  • 2
    date is very flexible on the output but the input is very specific. I'd suggest to transform the date before reaching date. May be bring in them from SQL functions or Javascript (depending on the source). If there is no source (like from a file), I'd suggest sed or awk
    – Juan
    Mar 25, 2019 at 23:15
  • ... or use something like Perl's Time::Piece with its strptime/strftime implementations
    – steeldriver
    Mar 25, 2019 at 23:26
  • or php -r "command" ...
    – Juan
    Mar 25, 2019 at 23:29

1 Answer 1

Reset to default
1

You might want to pre processes it first. My best shot is with sed

echo "28/11/2018 06:20:57 PM" | sed -E "s/([0-9]+)\/([0-9]+)\/([0-9]+) ([0-9]+):([0-9]+):([0-9]+) ([APM]{2})/\3\/\2\/\1 \4:\5:\6 \7/g" | xargs -I II date -d II "+%y-%m-%d %H:%M:%S"

Improve this answer
3
  • 1
    Great ! It works, Thank you so much . :) I used date because I had multiple column with dates different format in every column , it was working fine for other columns except this column because it has different date format with PM and AM in it.
    – Deepak Malhotra
    Mar 26, 2019 at 0:23
  • You are welcome. Don't forget to vote up ;)
    – Juan
    Mar 26, 2019 at 0:25
  • 1
    Thanks Juan, I did but as I am new to stackexchange, will need few more posts I think, before my upvotes start showing :)
    – Deepak Malhotra
    Mar 27, 2019 at 3:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.