The datatype BasedDate is a powerful tool to make some 'funky' application specific datatype for a date available as proper dates. It implements a simple but surprisingly often applicable algorithm :
date = <base> + ( value - 1 ) / <units-per-day>

<Base> (specified as YYYYMMDD) and <units-per-day> are set as a "misc" BINDING property.

One gets to pick only 1 setting for everything under a given binding.

As always the definition can be made using EXPORT BINDING (or MACHINE), edit, and IMPORT SYS .
Or the STUDIO GUI can be used. See picture below.

Here is a code quick example showing a simple 1 day per day from 'way-back' (1799).

$ nav_util export binding nav tt:
: <bindings>
<binding name='NAV'>
<datasources name='NAV'>
:
</datasources>
<environment name='NAV'>
<misc basedDate='17991231/int4//1' exposeXmlField='true' language='ENG'/>
:
</environment>
:
</binding>

</bindings>
</navobj>

The test uses a double-defined field to use able to insert by number or by date...




$ nav_util export table quest based_date_test tt:
:
<fields>

<field name='as_int' datatype='int4'/>
<field name='as_date' offset='0' datatype='based_date'/>
<field name='as_text' datatype='string' size='30'/>
</fields>
<keys/>
</table>
</navobj>




$ nav_util export table quest based_date_test tt:
<?xml version='1.0' encoding='ISO-8859-1'?>
Exporting table 'BASED_DATE_TEST'
<navobj version='5.3.3.5'>
<table name='based_date_test' datasource='quest' fileName=
'dka0:[hein]based_date.tmp'
organization='sequential'>
<fields>
<field name='as_int' datatype='int4'/>
<field name='as_date' offset='0' datatype='based_date'/>
<field name='as_text' datatype='string' size='30'/>
</fields>
<keys/>
</table>
</navobj>
OK

NAV_UTIL EXECUTE 'datasource_name'
insert into based_date_test (as_int, as_text) values ( 1, '1');

insert into based_date_test (as_int, as_text) values ( 200*366, '200*366');
insert into based_date_test (as_date, as_text) values ( '2013-04-17', '2013-04-17 ');
[B]select * from based_date_test;

Code:

Code:
as_int           as_date                      as_text


1                31-DEC-1799 00:00:00.00      1
73200            30-MAY-2000 00:00:00.00      200*366
77905            17-APR-2013 00:00:00.00      2013-04-17

note 1:

Initialy i made a copy & paste error using the userguide putting the name of the propery in the property:
<misc basedDate='basedDate="17991231/int4//1"'...
The gives the following error in NAV.LOG:
DT_BASED_DATE (1): ; Error: Based-date initialization error, base date value <0> is not valid
This article is posted mainly to allow GOOGLE hits for that string.

Any other syntax error in the basedDate property will lead to the same error. On output this will result in "null" values. On input the (handy!) default 1"9700101/int4//86400" which indicates the number of seconds since January 1 1970, which is the commonly used UNIX date representation.
Today (17-Apr-2013) became 1366243200 due to the bad initial specification


note 2:

As always, when using remote access you need to bounce the servers when changing meta-data as they may cache old definitions (ACADMIN)

note 3: yes, the final implementation will use 18000101 as base, note 12/31/1799 :-)

hth<
Hein

Name:  baseddate.PNG
Views: 224
Size:  15.4 KB