Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Convert the date format from mdy to ymd in column of file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Convert the date format from mdy to ymd in column of file
# 1  
Old 07-25-2011
Convert the date format from mdy to ymd in column of file
The date format in the delimited file for one column '6/27/2011 12:00:00 AM' Is it possible o change it to '2011-06-27 12:00:00 AM' for all the records..


Thanks in advance.....
# 2  
Old 07-25-2011
Tools change date format
Quote:
Originally Posted by infernalhell
The date format in the delimited file for one column '6/27/2011 12:00:00 AM' Is it possible o change it to '2011-06-27 12:00:00 AM' for all the records..


Thanks in advance.....
Hi,

Try this one,

Code:
nawk 'BEGIN{FS=" ";ORS=" ";}{split($2,a,"/");for(i=1;i<=NF;i++){if ( i == 2){print a[3]"/"a[2]"/"a[1];}else{print $i;}}print "\n"}' input_file

i assume,
$2=DateField

Input Sample:
Code:
SomeInfo 6/27/2011 12:00:00 AM
SomeInfo 6/27/2011 12:00:00 AM

Output:
Code:
 SomeInfo 2011/27/6 12:00:00 AM
 SomeInfo 2011/27/6 12:00:00 AM

Cheers,
RangaSmilie
Last edited by radoulov; 07-25-2011 at 10:44 AM.. Reason: Code tags.
# 3  
Old 07-25-2011
This would do the changes on the date format and print out all other lines as well (just assuming since you did not post a snippet of your input file):
Code:
awk -F"[/ ]" '/^.*\/..\/.... / {m=sprintf("%02d",$1); print $3"-"m"-"-$2,$4,$5; next}1' infile
2011-06-27 12:00:00 AM

# 4  
Old 07-25-2011
You have not told us much about your "delimited file". However, suppose for the purpose of demonstration that it (infile) is as follows:
Code:
'abc','06/27/2011 12:00:00 AM','def'
'ghi','06/28/2011 10:30:45 PM','jkl'

The following awk script:
Code:
awk 'BEGIN {FS=","; OFS=","}
     { nbr = split($2, a ,"[\047/ ]");
       $2 = sprintf("\047%s-%s-%s %s %s\047", a[4], a[2], a[3], a[5], a[6]);
       print $0
     }
' infile

will output
Code:
'abc','2011-06-27 12:00:00 AM','def'
'ghi','2011-06-28 10:30:45 PM','jkl'

# 5  
Old 07-25-2011
Hiii..One correction
Hii Murphy...

Thanks a lot for the quick turn around.....
1 Correction in here...

Please find the below code

Code:
awk 'BEGIN {FS="|"; OFS="|"}
     {        if (NR > 1)
{nbr = split($1, a ,"[\047/ ]");
       $1 = sprintf("\047%s-"0"%s-%s %s %s\047", a[4], a[2], a[3], a[5], a[6]);}
       print $0
     }
'  a.txt > b.txt

The problem is

My date is my date if 1 digit is coming as 6...But i would want it as 06
Ex:
Code:
'2011-07-1 12:00:00 AM'
'2011-07-01 12:00:00 AM'

I have added 0 for a[2] since i deal with the data of june and july....
Could u please help me with the date part....

Thanks a lot....
Last edited by Franklin52; 07-26-2011 at 05:38 PM.. Reason: Code tags
# 6  
Old 07-26-2011
Quote:
Originally Posted by infernalhell
The date format in the delimited file for one column '6/27/2011 12:00:00 AM' Is it possible o change it to '2011-06-27 12:00:00 AM' for all the records..
Code:
sed "s/'6/27/2011 12:00:00 AM'/'2011-06-27 12:00:00 AM'/" file

Smilie
# 7  
Old 07-26-2011
Hi,

Its actually for all the dates in that particular column..Not just a single occurrence...

I need a modification on Murphy's code where in I can calculate the lengths of a[3] and append 0s for that if it is singular digit...

---------- Post updated at 10:50 PM ---------- Previous update was at 10:31 PM ----------

Ive been trying the below but not getting the desired results....

Quote:
awk 'BEGIN {FS="|"; OFS="|"}
{ if (NR > 1)
{nbr = split($1, a ,"[\047/ ]");
{ if (length($(a[3])) == 1)
{$1 = sprintf("\047%s-"0"%s-"0"%s %s %s\047", a[4], a[2], a[3], a[5], a[6]);}
else
{$1 = sprintf("\047%s-"0"%s-%s %s %s\047", a[4], a[2], a[3], a[5], a[6]);}}

}
print $0
}
' a.txt > b.txt
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert Date Format

Hello, I want to change the format of date value in variable. e.g. cur_date = '2013/03/13 14:24:50' (yyyy/mm/dd hh24:mi:ss) I want to change this to '13-MAR-2013 14:24:50 Following code coverts the current date to format I am looking for. But I do not know how this can be done for a date... (8 Replies)
Discussion started by: cartrider
8 Replies

2. Shell Programming and Scripting

How to change the format of the date column in a flat file?

Hi, i have a flat file namely temp.txt with this data below ID|name|contact_date 101|Kay|2013-12-26 102|let|2013-12-26 I need to modify the date data in the flat file into MM/DD/YYYY HH24:MI:SS format let me know the code for this. Thank you! (5 Replies)
Discussion started by: srikanth_sagi
5 Replies

3. Shell Programming and Scripting

Convert date column as yyyy/mm/dd format

Hi All, I have file like “April 10, 2013”,”raj” “April 29, 2013”,”raj1” Output : “2013/04/10”,”raj” “2013/04/29”,”raj1” Please help me how to do... (9 Replies)
Discussion started by: bmk
9 Replies

4. Shell Programming and Scripting

Need a unix script to convert date into Julian format in a text file

The 6th & 7th column of the text files represents date & time. I need this to be converted in julian format using command "date +%s -d <date>". I know the command, but dont know how to use it on the script 0 dbclstr-b IXT_Web Memphis_Prod_SQL_Full Memphis-Prod-SQL-Full-Application-Backup... (4 Replies)
Discussion started by: ajiwww
4 Replies

5. Shell Programming and Scripting

convert date format

I've been using this thread: https://www.unix.com/shell-programming-scripting/58675-change-date-dd-mmm-yyyy-mm-dd-yyyy.html and https://www.unix.com/shell-programming-scripting/14655-changing-yyyy-mm-dd-ddmmyy.html and this code: on this format: 05/16/2008 18:30:49 Installation 48985and I'm... (3 Replies)
Discussion started by: dba_frog
3 Replies

6. Shell Programming and Scripting

Convert comma text file to Column in html format

I am trying to generate a report with below file : File1 : EQADM,edrtere9-phys,8122caef0,gpatmon,/bin/ksh,nuten Erick EQADM,edrtere11-phys,8227caef0,gpatmon,/bin/ksh,nuten Erick EQADM,edrtere3-phys,822caef0,gpatmon,/bin/ksh,nuten Erick can you help me convert it to html and add... (9 Replies)
Discussion started by: sriram003
9 Replies

7. Shell Programming and Scripting

convert date format to mysql date format in log file

I have a comma delimited log file which has the date as MM/DD/YY in the 2nd column, and HH:MM:SS in the 3rd column. I need to change the date format to YYYY-MM-DD and merge it with the the time HH:MM:SS. How will I got about this? Sample input 02/27/09,23:52:31 02/27/09,23:52:52... (3 Replies)
Discussion started by: hazno
3 Replies

8. Shell Programming and Scripting

Convert Epoch time format to normal date time format in the same file

I have a file named "suspected" with series of line like these : {'protocol': 17, 'service': 'BitTorrent KRPC', 'server': '219.78.120.166', 'client_port': 52044, 'client': '10.64.68.44', 'server_port': 8291, 'time': 1226506312L, 'serverhostname': ''} {'protocol': 17, 'service': 'BitTorrent... (3 Replies)
Discussion started by: rk4k
3 Replies

9. UNIX for Dummies Questions & Answers

Convert date format

Hi All, Need your help in converting a date format in ksh. I'm currently working on SUN os where my script is getting a date from a table. The result returns to ksh in this format: 17-JUL-08 How do i convert this string to a date format like yyyymmdd? I tried #!/bin/ksh d="17-JUL-08"... (5 Replies)
Discussion started by: agathaeleanor
5 Replies

10. Shell Programming and Scripting

convert mmddyy date format to ccyyddd format??

hi, for reading a cobol indexed file i need to convert "mmddyy" date format to "ccyyddd" format. i checked the datecalc and other scripts but couldnt modify them to cater to my need:(... The datecalc gives an output which i believe is the total days till that date, but i want to convert it... (2 Replies)
Discussion started by: Bhups
2 Replies
Login or Register to Ask a Question