Get CSV Column Value as a Date Value (CSV_DATE) UDF

Index

Schema IQUERY

CSV_DATE( csv_data, header => 'column header', fmt=> 'ISO' );

CSV_DATE( csv_data, ID => relative-column ID, fmt=> 'ISO' );

The CSV Function parses the csv_data passed to it and returns the data for the column identified by the 2nd parameter. The 2nd parameter may be the relative column number or the column heading.

This Function is primarily used with the iQuery CSV() UDTF to parse the CSV content.

Returned Value

DATE

This UDF returns the column content as a Date or NULL if the date content in the CSV is invalid. The INPUT The value can be cast to the length and decimal positions desired, but can also typically be Fetched into a host variable that is the right size without the need to cast it. For example if the accumulated 30-years sales figures are more than 31 digits, then casting the column might look like the following:

 CAST( iQuery.csv_bigdec(data, 'Lifetimesales') as dec(37,2) )

Parameters

CSV_DATA

Expected value: DATA

The column DATA is returned from the iQuery CSV() UDTF. The parameter should be the column named "data" as returned by the CSV() UDTF. Any other value indicates customization of the interface and may produce undesirable results.

HEADER

When the HEADER version of this UDF is used, the column heading (enclosed in single quotes) is specified to identify the column whose data is to be returned.

ID

When the Relative Column ID version of this UDF is used, ID identifies the relative column number whose data is returned.

FMT

Default: ISO

The date format as it appears in the CSV data. For example, if the date appears in the CSV data as 210315 (YMD) then this parameter would be set to fmt=>'ymd'. The following formats are supported:

Other formats are supported based on the SQL Scalar Function TIMESTAMP_FORMAT, however only a 10-position value is extracted for DATEs. As of this writing, time and timestamp values are not supported but may be in a future release via a CSV_TIMESTAMP() UDF.

The Date Separator is determined by examining the CSV content itself. If it contains dash, slash, or period, that symbol is automatically incorporated into the Date Format pattern used to convert the textual CSV content to a true Date value.

NOTE: For Time and Timestamp values, customers may use the CSV_VAL() UDF to extract the time or timestamp content and then cast it to the proper time or timestamp format. For example:

CSV Content:

EmpNo,Empname, Clock In, Clock Out
23011,"Han Solo",2021-06-21 07:52:17, 2021-06-21 16:58:33

SQL Statement:

select timeStamp_Format( rTrim( iQuery.csv_val(data,'ClockOut')), 'YYYY-MM-DD HH24:MI:SS')
    from table( iQuery.csv('/home/timeclock/clocking.csv')) clocking;

In this example, the company's employees are clocking in and out. The CSV UDTF is used to read the data that the time clock device writes to the IFS. The CSV_VAL() function extracts the ClockOut content and passes it to the TIMESTAMP_FORMAT function, which converts it to a valid TimeStamp.