New post
Question
· Jun 23, 2020

New Column in table showing with 1840-12-31 00:00:00

#Relational Tables #SQL #Caché

Hi:

We added a column to a table as follows 

Property SentTime As Ens.DataType.UTC;

And the code to populate this

if pStatus = "SENT" //set SentTime which is used in the Tableau Report
{
Set doc.SentTime = $$$timeUTC
}

For Historical records of type "Rejected" the Sent time is appearing though as 1840-12-31 00:00:00, we don't know why. Current docs work fine with this column just historical seems to display the default instead of blank/null. 

Discussion (6)0
Log in or sign up to continue
Robert Cemper · Jun 23, 2020

OK. I understand those records where created BEFORE you defined your new column.
I checked the internal code

$s(%val="":"",1:$zdatetime($zdTH($zdatetimeh(%val,3,,,,,,,,0),-3),3,,3))

This tells me:
- Your value is Not Empty 
- it has no valid time format, so it is trapped by the ERROR option and set to Zero  ==>> 1840-12-31 00:00:00

Suggestion:
- set value to NULL for old records.
or
- create your own data type for this case. 
or

- use a calculated Property  to trap the values

1 0
Mark O'Reilly · Jun 24, 2020

Thanks for this the outcome was:

There was a field with nulls or 0's that must not have been in our development environment (guess). This could be seen by looking at the D Global for the table in the globals this value or viewing SQL in logical mode. The option would have been for us-

-Remove new row info add back in the old column and add the new one 

-Accept the mistake and set value to null for the old data 

We went with the later. The code that used to set this old field also seemed to not be in our dev or live environment nor this column that was overwritten being any use. Just a lesson in check your code when you're overwritting, check the class definition for a table especially the bit at the end with the words Value ect that explain the order of the data, look at the GlobalD for the class as well to see the data stored in a comma seperated value. 

0 0