Convert Integer to TimeStamp Data Type

, , , , ,
1

It seems that all the date fields (they are n8 on the mainframe) are exposed in the CDD as integer.

I need to be able to do calculation between these date-value fields with current_date(). I know that the Timstampdiff function is available.

Does CONNX have a function which allows me to convert these date-value integer data type to a timestamp data type?

Alternatively, is there a way to have the date-value fields exposed as date fields in SQL?

Thanks,
Min

2

The SQL Gateway knows and supports the NATRUAL Date/Time/Timestamp data types.

DMM Fields defined as with the data types ‘D’ or ‘T’ are automatically imported
with the data types ‘NATURAL DATE’ or ‘NATURAL TIME’

The data type of a Field can also be cahnged after the importing - using the CDD manager -
by selecting the NATIVE DATA TYPE -

  • Adabas Natural Date (Date Only)
  • Adabase Natural Time (Time Only)
  • Adabas Natural Timestamp (Date and Time)

Timestamp.JPG
Timestamp.JPG1138×473 115 KB

3

Min’s issue here is that the database fields are not from the Natural D format, so it is not getting the benefit of built-in typecasting to other date formats. Programmatically the values are expressed as YYYYMMDD but to both Natural and Adabas these are just any N8/P8/U8 number.

I am not sure how you can fix this, Min. Had it been a DDM that used Natural’s D format, you wouldn’t have this problem.

4

Dates/Times values (‘YYYYMMDD’) stored in Adabas Fields with field-types - U8, P8, A8
can be interpretted by CONNX as Date/Time values.

To achieve this -

  • import the the table definition
  • edit the column definition using the CDD Administrator
  • change the Native Type as required

In the provided sample the data was stored in the form ‘YYYYMMDD’

FDT.PNG
FDT.PNG746×158 4.86 KB

5

Sorry the screenshots did not upload correctly

Here are all screenshots in a ZIP-File
NUMERIC_DATE.zip (78.8 KB)

6

Richard,

I did tried changing the native type to Text Date(YYYYMMDD) in the CDD Data Dictionary tool after the import. While this allows me to do the select with timstampdiff, the change in the data type gives me an adabas resp code 44 when I tried to insert/update a value into this column. Resp code 44 is pointing to format buffer error.

My update looks like this:

update table_name set date_value_column_name = ‘20110304’ or
update table_name set date_value_column_name = 20110304

Any suggestion?

Thanks,
Min

7

Please open a support issue, so that this can be analyzed …

8

The issue is that the value is being interpretted as a CHARACTER-String

Using the DATE-Literal syntax {d’YYYY-MM-DD’} functions correctly -

INSERT into NUMERIC_DATE_MAPPED
VALUES ({d’1234-03-03’}, {d’1234.02.01’}, {d’1234.01.01’})

9

Richard,

Thank you for this.

Min