# Convert Integer to TimeStamp Data Type **URL:** \ **Category:** Forum\ **Tags:** Adabas-Natural, Adabas, Adabas-Add-on-Products, SQL-Access, Mainframe-Integration, CONNX\ **Created:** [June 5, 2012, 5:25pm UTC](https://techcommunity.softwareag.com/t/convert-integer-to-timestamp-data-type/100663 "2012-06-05T17:25:34Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Min\_Chai](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@Min\_Chai](https://techcommunity.softwareag.com/u/Min_Chai)\ **Post date:** [June 5, 2012, 5:25pm UTC](https://techcommunity.softwareag.com/t/convert-integer-to-timestamp-data-type/100663/1 "2012-06-05T17:25:34Z")
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 ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [June 6, 2012, 8:36am UTC](https://techcommunity.softwareag.com/t/convert-integer-to-timestamp-data-type/100663/2 "2012-06-06T08:36:48Z")
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](https://global.discourse-cdn.com/techcommunity/original/2X/d/d8d8c9d28baa055450ce4537b6245c2c6dd69fff.jpeg) ---
**Author:** ![Brian\_Johnson1](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/brian_johnson1/32/163_2.png) [@Brian\_Johnson1](https://techcommunity.softwareag.com/u/Brian_Johnson1)\ **Post date:** [June 7, 2012, 5:40pm UTC](https://techcommunity.softwareag.com/t/convert-integer-to-timestamp-data-type/100663/3 "2012-06-07T17:40:38Z")
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. ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [June 11, 2012, 11:42am UTC](https://techcommunity.softwareag.com/t/convert-integer-to-timestamp-data-type/100663/4 "2012-06-11T11:42:57Z")
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](https://global.discourse-cdn.com/techcommunity/original/2X/b/bdc3a730e790f581093cc60e15ce3be0c2bd5a10.png) ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [June 11, 2012, 11:44am UTC](https://techcommunity.softwareag.com/t/convert-integer-to-timestamp-data-type/100663/5 "2012-06-11T11:44:44Z")
Sorry the screenshots did not upload correctly Here are all screenshots in a ZIP-File [NUMERIC\_DATE.zip](https://techcommunity.softwareag.com/uploads/short-url/uzv4ICJLbmYd5PRPLLEDdpm4pNm.zip) (78.8 KB) ---
**Author:** ![Min\_Chai](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@Min\_Chai](https://techcommunity.softwareag.com/u/Min_Chai)\ **Post date:** [June 13, 2012, 1:32pm UTC](https://techcommunity.softwareag.com/t/convert-integer-to-timestamp-data-type/100663/6 "2012-06-13T13:32:01Z")
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 ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [June 13, 2012, 2:08pm UTC](https://techcommunity.softwareag.com/t/convert-integer-to-timestamp-data-type/100663/7 "2012-06-13T14:08:07Z")
Please open a support issue, so that this can be analyzed … ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [June 13, 2012, 2:17pm UTC](https://techcommunity.softwareag.com/t/convert-integer-to-timestamp-data-type/100663/8 "2012-06-13T14:17:06Z")
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’}) ---
**Author:** ![Min\_Chai](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@Min\_Chai](https://techcommunity.softwareag.com/u/Min_Chai)\ **Post date:** [June 14, 2012, 4:09pm UTC](https://techcommunity.softwareag.com/t/convert-integer-to-timestamp-data-type/100663/9 "2012-06-14T16:09:22Z")
Richard, Thank you for this. Min ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [April 2, 2021, 3:02pm UTC](https://techcommunity.softwareag.com/t/convert-integer-to-timestamp-data-type/100663/10 "2021-04-02T15:02:02Z")