New post
Question
Oliver Wilms · Jan 8, 2022

SQL LOAD DATA date values

#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
0
0 237
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
Oliver Wilms · Jan 9, 2022

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

0 0
Robert Cemper · Jan 9, 2022

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

0 0
Oliver Wilms · Jan 9, 2022

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

0 0
Oliver Wilms · Jan 9, 2022

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?

0 0
Robert Cemper · Jan 9, 2022

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 ];
0 0
Eduard Lebedyuk · Jan 10, 2022

Is there a way to also not store InputDate?

0 0
Oliver Wilms · Jan 10, 2022

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.

0 0
Robert Cemper · Jan 10, 2022

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,  :-((
 

0 0
Eduard Lebedyuk · Jan 11, 2022

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.

0 0