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.
Convert DB2 date fields to Vertica format
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Convert DB2 date fields to Vertica format
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
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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.