Year stored is wrong while uploading data from CSV into IRIS
Hello Community,
I am trying to load data from a CSV into IRIS. I am using a record mapper. The CSV has 3 columns which are dates and I am using $ZDATEH() to convert the date columns to IRIS internal format. But the year that is getting loaded in IRIS is wrong. Example: if in my csv the date is "5/26/23", after data transformation and loading the data, date stored in the IRIS is "05/26/1923". The year getting stored is wrong, instead of 2023 the year getting stored is 1923. Can you please tell me how to solve this? Attaching screenshot of Data Transformation builder for reference.
Thank you!
.png)
There are provisions in the $ZDATEH function to cover the "two-digit-year" century issue. If you need all two-digit years to equate to 20xx, try this:
$ZDATEH("01/01/23",,,3,58074,94598)As in, set the 'yearopt' parameter to 3, then set the startwin & endwin dates (in $H format) to the beginning and ending window to interpret the two-digit years.
If you need a specific range 100 year range - for this example, to evaluate two-digit years between 01/01/1950 and 12/31/2049, you'll need the $H values of those dates:
W $ZDATEH("1/1/1950") 39812 W $ZDATEH("12/31/2049") 76336Then use those $H values in the window of the $ZDATEH command:
$ZDATEH("01/01/23",,,3,39812,76336)Here's more documentation on the $ZDATEH command:
https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RCOS_fzdateh
Hope this helps!
Thanks!
The reason behind the conversion is Cache identifies/assume from 1900 - 1999 year if you send two digit for year. So, You should send four digit year. attached the documentation "Two or four digits may be specified for years in the range 1900 to 1999. Four digits must be specified for years before 1900 or after 1999."
Thank you!
Hi @Anup Thakkar,
the simplest way to always stay in the current century is :
$ZDATEH("05/26/23",,,6)You just need to use year-opt = 6 to get all dates with two-digit years in the current century .
w $zdt($ZDATEH("05/26/23",,,6),3) 2023-05-26 w $zdt($ZDATEH("05/26/1923",,,6),3) 1923-05-26Thank you!
You're welcome @Anup Thakkar