Loading Data with Different Date Formats in GBase 8a — Per‑Column Control

When importing data into GBase 8a, you often encounter CSV files where different date columns follow different formats. Instead of pre‑processing the file with complex ETL, GBase 8a allows you to specify a separate date format for each column directly in the LOAD DATA INFILE command. This article shows you exactly how to do it in your gbase database.

Sample Table

Create a table with two date columns:

gbase> CREATE TABLE td1(id INT, d1 DATE, d2 DATE);
Query OK, 0 rows affected (Elapsed: 00:00:00.18)

Sample Data File

The file td1.txt is comma‑separated. The first date is in YYYY-MM-DD format, while the second is in MM-DD-YYYY format.

1,2021-1-1,02-03-2021

Loading with Per‑Column Date Formats

Use the TABLE_FIELDS option to define the format for each date column. The syntax is column_name data_type "format_string".

gbase> LOAD DATA INFILE 'sftp://gbase:gbase1234@10.0.2.103/home/gbase/td1.txt'
  INTO TABLE td1
  FIELDS TERMINATED BY ','
  TABLE_FIELDS 'id, d1 date "%Y-%m-%d", d2 date "%m-%d-%Y"';
Query OK, 1 row affected (Elapsed: 00:00:00.65)
Task 2061 finished, Loaded 1 records, Skipped 0 records

With this single command, d1 is parsed with the YYYY-MM-DD mask and d2 with MM-DD-YYYY. The data is loaded correctly without any prior file manipulation.

When This Technique Shines

This per‑column date format feature is particularly useful when:

• You receive data feeds from multiple sources where date conventions vary.
• You're migrating historical data with inconsistent date representations.
• You want to avoid writing custom scripts to normalise dates before loading.

By pushing the format logic directly into the load statement, you keep your ingestion pipeline lean and your gbase database loading performance at its peak.