Page 1 of 1
date conversion for MySQL
Posted: Tue Aug 15, 2006 1:06 pm
by rf
I need a grep or FSR or AWK or some miniprogram/statement that will convert 08/16/2006 date format into 2006-08-16 format in a text file. I am sure this would take an expert 30 seconds to do!
Thanks much
rf
Posted: Tue Aug 15, 2006 2:20 pm
by deww
You can use date...
date +%Y-%m-%d
Posted: Tue Aug 15, 2006 4:08 pm
by rf
Thanks for the response.
Give me an example of how to use:
You can use date...
date +%Y-%m-%d
rf
Posted: Tue Aug 15, 2006 4:59 pm
by deww
rf wrote:Thanks for the response.
Give me an example of how to use:
You can use date...
date +%Y-%m-%d
rf
The example is already there... I don't know exactly what you're trying to accomplish there myself.
Posted: Tue Aug 15, 2006 5:23 pm
by rf
In a text file there is a date in each line in the form mm/dd/yyyy.
This date form needs to be converted into the form yyyy-mm-dd.
I am working in Linux.
I tried from a terminal control line # date+%y-%m-%d and get the response "command not found"
I tried it in a text editor replacing "08/20/1996" with "date+%y-%m-%d" and get the literal "date+%y-%m-%d" as the replacement.
Now I know two ways not to use it!
I can continue doing the literal replacement in the replace function of a text editor - but I am sure there is a better way! (especially with a thousand lines of text!)
At my level of experience, I need an example that tells me - in a text editor, or from the Linux TLC type this in "and spell it out exactly what I need to type in" etc.
Thanks for your help.
rf
Posted: Tue Aug 15, 2006 5:32 pm
by munky
awk -F"/" '{print $3 "-" $1 "-" $2}' somefile.txt
Posted: Tue Aug 15, 2006 6:51 pm
by rf
It looks like that should work.
This is my experience:
The first line in the text file is:
"Some Name 1023 01/13/2005 BHHEC 58 0 1 64 140 NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI 3 3 NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI 0.444 -1.3 -0.6 2 SD check it NULLI none NULLI NULLI NULLI yes N NULLI NULLI NULLI yes N none 1 3 NULLI carrot juice 1 NULLI 0 1 2 "
# awk -F "/" '{print $3 "-" $1 "-" $2}' testbmddata
gives me this output on my screen:
"2005 BHHEC 58 0 1 64 140 NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI 3 3 NULLI NULLI NULLI NULLI NULLI NULLI NULLI NULLI 0.444 -1.3 -0.6 2 SD check it NULLI none NULLI NULLI NULLI yesN NULLI NULLI NULLI yes N none 1 3 NULLI carrot juice 1 NULLI 0 1 2-Some Name 1023 01-13
gawk or awk makes no difference.
The target is found and the / are replaced with -, however the line is ended after the year ($3) and the first part of the line is put at the end of the line.
awk is a great program but I have not got it right yet.
Thanks
Posted: Wed Aug 16, 2006 12:15 am
by munky
man awk
-F defines what the spacer is
-F"/" means your line has only 3 parts ("Some Name 1023 01" would be $1)
cat yourfile.txt | perl -e 'while(<>){chomp; $_ =~ s/(\d{1,2})\/(\d{1,2})\/(\d{4})/\3-\1-\2/; print "$_\n";}'
Posted: Wed Aug 16, 2006 12:30 am
by rf
Followup:
Thanks for listening and responding. In FSR this program worked perfectly!
:file named date.fsr
:program to change date from mm/dd/yyyy format to yyyy-mm-dd format
"{[0-9][0-9]}/{[0-9][0-9]}/{[0-9][0-9][0-9][0-9]}" "{3}-{1}-{2}" -c
It is a DOS program! I am glad we still have DOS!
Thank you
rf
Posted: Wed Aug 16, 2006 3:04 am
by deww
Woops! Guess I should actually read the actual post!
*deletes his other posts*