SQL LOAD DATA date values

Question

Question

Oliver Wilms · Jan 8, 2022

#Data Import and Export #SQL #InterSystems IRIS

I have csv date file with date values like this "4/10/2021" for April 10, 2021. I defined a table with this property: Property TranDate As %Library.Date.

I capture error

[SQLCODE: <-104>:<Field validation failed in INSERT, or value failed to convert in DisplayToLogical or OdbcToLogical>] [Location: <ServerLoop>] [%msg: <Field 'dc_data_finance.transact.TranDate' (value '4/10/2021') failed validation Field ...

I do not really want to change TranDate to %String. How can I import "4/10/2021" into %Date property?

Product version: IRIS 2021.2

$ZV: IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2021.2 (Build 617U) Thu Dec 9 2021 15:00:18 UTC

Discussion (10)1

Log in or sign up to continue

Muhammad Waseem · Jan 8, 2022

Hi

Modify date format in your CSV to yyyy-mm-dd and then try.

Thanks

0 0

score 0 · Answer 1 · 2022-01-09T18:30:11-05:00

I created a util class with a classmethod to read the CSV and update the date format to yyyy-mm-dd. Thanks for the suggestion, Muhammad

score 0 · Answer 2 · 2022-01-09T03:19:00-05:00

in a normal SQL INSERT using TO_DATE('12/5/1948','DD/MM/YYYY') works inside VALUES()
just a guess for LOAD DATA

score 0 · Answer 3 · 2022-01-09T12:05:38-05:00

Thank you for the suggestion, Robert. I tried to include TO_DATE inside VALUES, but it did not work with LOAD DATA

score 0 · Answer 4 · 2022-01-09T12:10:02-05:00

I wonder if a method like TranDateODBCToLogical is executed when LOAD DATA tries to INSERT a row of data or is there any trigger that I could add to the class definition to convert the value "4/10/2021" to a %Date logical value?

score 0 · Answer 5 · 2022-01-09T12:45:57-05:00

If the table is based on an IRIS Class (ii.e. not generated by DDL)
You may try

/// helper for input
Property InputDate As %String;
/// real used date
Property MyDate As %Date [ SqlComputeCode = { set {*} =$ZDH({InputDate},4)},
                            SqlComputed, SqlComputeOnChange = InputDate ];

score 0 · Answer 6 · 2022-01-10T15:39:26-05:00

Eduard Lebedyuk · Jan 10, 2022

Is there a way to also not store InputDate?

0 0

score 0 · Answer 7 · 2022-01-10T16:39:19-05:00

Eduard,

Ideally I would insert directly into date property from text value format 'MM/DD/YYYY'. I do not think we can use TO_DATE() or other SQL function with LOAD DATA. I decided to use a util class to reformat the date column and write a reformatted text file to use as input for LOAD DATA.

score 0 · Answer 8 · 2022-01-10T17:04:00-05:00

SQL needs to write to a persistent element.
A TRIGGER may work eventually if it runs BEFORE data validations - not sure
OR you fiddle around the data type and do the conversion there in the Code Generator for ClassMethod OdbcToLogical to accept a 2nd format, :-((

score 0 · Answer 9 · 2022-01-11T06:04:00-05:00

There's a FORMAT property parameter. Maybe setting it would help @Oliver Wilms?

Checked, it's only for Display conversions.

I suppose a custom data type would work.