DMSII Dates

Even though DMSII does not have a date data type, most DMSII sites use several common methods to store dates. This section includes ways to decode these types of date representations into a relational database date data type.

The DATABridge Client supports the following DMSII date encoding methods:

For	That represents dates as	See
DMSII GROUPS	Three numbers for year, month, and day	DMSII Dates Represented as a GROUP of Numbers
DMSII NUMBER values	Any of the following: MISER database dates, usually NUMBER(5) LINC database dates Month, day, and year combined into a six-digit (031005) or eight-digit (03102005) number Julian dates represented as a five-digit number (06905) or seven-digit number (0692005)	Decoding DMSII Dates Represented as ALPHA or NUMBER
DMSII ALPHA values	Any of the following: LINC database dates Month, day, and year represented by a 6- or 8-digit alpha string containing only digits Delimited dates such as (03/10/10) Dates with three-character month names (MAR102005)	Decoding DMSII Dates Represented as ALPHA or NUMBER
DMSII REAL values	Any of the following: Month/year without day or other unique variations Non-Standard dates Month/year without day or other unique variations	DMSII Times Represented as ALPHA, NUMBER, or TIME -or- Decoding DMSII Date/Time Represented as ALPHA or NUMBER -or- Unique DMSII Date/Time Represented as ALPHA or NUMBER

Choosing the SQL Data Type of the Relational Database Column

Regardless of the original DMSII date structure, the resulting relational database column has a sql_type of 12 (smalldatetime) in the case of SQL Server and a sql_type of 10 (date) in the case of Oracle.

To make the Client map a DMS item to a column that is a date data type, you must set the bit DIOPT_Clone_as_Date (2) in the di_options column of the corresponding DMS_ITEMS entry using the script.user_layout.dataset user script.

SQL Server supports the multiple date data type. You can make the Client generate different types of dates by using the script.user_layout.dataset user script to set the following bits in the di_options column of the corresponding DMS_ITEMS table entry:

DIOPT_UseLongDate (128) causes the Client to a use a data type of 10 (datetime) instead of smalldatetime. In the case of SQL Server 2008 you can also make the client use the date or datetime2 data types.
DIOPT_UseLongDate2 (65536) causes the Client to use the datetime2 data type. If both this bit and the DIOPT_UseLongDate bit are set, datetime2 is used.

DIOPT_Clone_as_DateOnly (32768) causes the Client to use the date data type which is 3-bytes long and contains no time.

Relational Database Date Data Type	Value for sql_type Column
Microsoft SQL Server: datetime (8 bytes)	10
Microsoft SQL Server: smalldatetime (4 bytes)	12
Oracle: date (7 bytes)	10
Microsoft SQL Server: int Oracle: number(10) Note: The date is formatted according to the numeric_date_format configuration parameter, whose default value is 23 (mmddyyyy).	13
DB2: timestamp	10
Microsoft SQL Server 2008: datetime2 (8 bytes)	19
Microsoft SQL Server 2008: date (3 bytes)	20

For an example script, see Changing SQL Data Types.