date conversion for MySQL

Post everything else here

Moderators: Website/Forum Admins, Other/Off Topic Moderators

rf
Posts: 30
Joined: Wed Apr 05, 2006 10:16 pm

date conversion for MySQL

Postby rf » Tue Aug 15, 2006 1:06 pm

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 :D

rf
User avatar
deww
Posts: 125
Joined: Fri Jul 18, 2003 7:17 pm

Postby deww » Tue Aug 15, 2006 2:20 pm

You can use date...
date +%Y-%m-%d
rf
Posts: 30
Joined: Wed Apr 05, 2006 10:16 pm

Postby rf » Tue Aug 15, 2006 4:08 pm

Thanks for the response. :)

Give me an example of how to use:
You can use date...
date +%Y-%m-%d
rf :D
User avatar
deww
Posts: 125
Joined: Fri Jul 18, 2003 7:17 pm

Postby deww » Tue Aug 15, 2006 4:59 pm

rf wrote:Thanks for the response. :)

Give me an example of how to use:
You can use date...
date +%Y-%m-%d
rf :D
The example is already there... I don't know exactly what you're trying to accomplish there myself.
rf
Posts: 30
Joined: Wed Apr 05, 2006 10:16 pm

Postby rf » Tue Aug 15, 2006 5:23 pm

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 :D
User avatar
munky
Site Admin
Posts: 826
Joined: Wed Jul 02, 2003 4:54 pm
Location: Phoenix AZ
Contact:

Postby munky » Tue Aug 15, 2006 5:32 pm

awk -F"/" '{print $3 "-" $1 "-" $2}' somefile.txt
In God we trust,
Everyone else must have an X.509 certificate.
rf
Posts: 30
Joined: Wed Apr 05, 2006 10:16 pm

Postby rf » Tue Aug 15, 2006 6:51 pm

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 :D
User avatar
munky
Site Admin
Posts: 826
Joined: Wed Jul 02, 2003 4:54 pm
Location: Phoenix AZ
Contact:

Postby munky » Wed Aug 16, 2006 12:15 am

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";}'
In God we trust,
Everyone else must have an X.509 certificate.
rf
Posts: 30
Joined: Wed Apr 05, 2006 10:16 pm

Postby rf » Wed Aug 16, 2006 12:30 am

Followup: :D

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 :D

rf
User avatar
deww
Posts: 125
Joined: Fri Jul 18, 2003 7:17 pm

Postby deww » Wed Aug 16, 2006 3:04 am

Woops! Guess I should actually read the actual post! :D *deletes his other posts*

Who is online

Users browsing this forum: No registered users and 6 guests