@DorukK wrote:
In the database the values are not 01JAN1960. When we set the format as datetime24. it returns 01JAN1960
This likely indicates that somewhere in the process you have a value extremely close to if not exactly zero.
I might suggest running this and showing the result:
proc sql outobs=22;
select put(AuditInformation_CreatedDate, best16.) from &sas_db_libref_idmap..Customer;
quit;
The 27. format would round anything between -0.5 and 0.5 to zero, so your format choice may be hiding something.
If you still see zero then that is the value sent across from your connection to the external data base. At that point I would ask how you know the values you are showing with the 01Jan1960 date portion are from the same records as the dates you show in the first post? Suggest that you consider looking at some identification variables and make sure you are comparing the same records.
I am guessing that your picture of the variable properties in the external database that has a "not null" as part of the description means that there must be something in the field. So you may just possibly find out that 0 (zero) is a default date when a more interesting value is not yet assigned such as an Audit has not been created yet. You might check to see if the data set is sorted by that value.
... View more