Page 1 of 1

Convert DB2 date fields to Vertica format

Posted: Thu Jul 14, 2016 2:08 pm
by beth
Hi all,

Anyone now an easy way to load dates into Vertica that come from a DB2 database? The format looks like this:

2016-19-21-03.21.22.70796

I have a data file that is full of these fields.

Re: Convert DB2 date fields to Vertica format

Posted: Thu Jul 14, 2016 2:36 pm
by JimKnicely
You can use Perl to convert the data format...

If this is your file:
$ cat db2date.txt
2013-09-21-07.20.25.70796

… then you go like this:
$ perl -pe 's/(\d{4}-\d{2}-\d{2})-(\d{2})\.(\d{2})\.(\d{2})\.(\d+)/$1 $2:$3:$4.$5/g' db2date.txt > isodate.txt

… to get:
$ cat isodate.txt
2013-09-21 07:20:25.70796