Converting dates in text file to mysql format

[starlight2003]

Hi

I have several text files with data which looks like this:

01.02.2009 Søndag 06:59 08:29 12:36 14:33 16:33 18:03

02.02.2009 Mandag 06:57 08:27 12:36 14:35 16:36 18:06

I need to convert the dates from the format DD.MM.YYYY to
YYYY-MM-DD

For example the occurence of 01.02.2009 must be changed to
2009-02-01 and keeping the rest of the data as it is. By doing this I can import the files into MySQl and filter the times based on a specific date.

Thanks.

 

[feherke]

Hi

I need to convert the dates from the format DD.MM.YYYY to
YYYY-MM-DD

Not necessary. Using the [tt]str_to_date()[/tt] function you can insert the date without reformat it :

awk "print \"insert into table values (str_to_date('\" \$0 \"','%d.%m.%Y')" /input/file | mysql -D database

But anyway, you van easily modify the format with regular expressions :

awk 'split($1,d,/\./)==3{$1=d[3]"-"d[2]"-"d[1]}1' /input/file

awk '$1=gensub(/(.+)\.(.+)\.(.+)/,"\\3-\\2-\\1","",$1)' /input/file

Note that the above methods will make the field separators to be replaced with the [tt]OFS[/tt] string. To avoid that, you have to access the date part as substring, not as field :

awk 'split(substr($0,1,10),d,/\./)==3{$0=d[3]"-"d[2]"-"d[1]substr($0,11)}1' /input/file

Feherke.

http://rootshell.be/~feherke/

 

[starlight2003]

Thanks for the help!.