Convert DB2 date fields to Vertica format

Moderator: NorbertKrupa

Post Reply
beth
Intermediate
Intermediate
Posts: 58
Joined: Tue Oct 01, 2013 12:42 pm

Convert DB2 date fields to Vertica format

Post by beth » Thu Jul 14, 2016 2:08 pm

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.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Convert DB2 date fields to Vertica format

Post by JimKnicely » Thu Jul 14, 2016 2:36 pm

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
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica SQL”