SQL LOAD DATA date values
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
Hi
Modify date format in your CSV to yyyy-mm-dd and then try.
Thanks
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
in a normal SQL INSERT using TO_DATE('12/5/1948','DD/MM/YYYY') works inside VALUES()
just a guess for LOAD DATA
Thank you for the suggestion, Robert. I tried to include TO_DATE inside VALUES, but it did not work with LOAD DATA
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?
If the table is based on an IRIS Class (ii.e. not generated by DDL)
You may try
Is there a way to also not store InputDate?
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.
SQL needs to write to a persistent element. ClassMethod OdbcToLogical to accept a 2nd format, :-((
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
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.
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue