Mapping Time, TimeStamp, and Date Primitive Types to the SQL Server DATETIME Type

Product Information > JADE Synchronized Database Service (SDS) Administration Guide > Chapter 2 - Relational Population Service (RPS) Support > Mapping Time, TimeStamp, and Date Primitive Types to the SQL Server DATETIME Type

Mapping Time, TimeStamp, and Date Primitive Types to the SQL Server DATETIME Type

SQL Server 2000 and 2005 do not have separate date and time data types.

When mapping the Date, Time, and TimeStamp primitive types in SQL Server 2000 and 2005:

The JADE TimeStamp and Time primitive types include milliseconds, which are rounded in the SQL Server DATETIME data type.

To save the exact millisecond value, map the time or timestamp property to an INTEGER data type.
Due to the values allowed in an SQL Server DATETIME field, if the value of a Date primitive type or the Date value of a TimeStamp primitive type is less than January 1, 1753 or greater than December 31, 9999, the value is marked as NULL and a message output to the jommsg.log file.

SQL Server 2008 and higher has separate date and time data types. Valid dates are in the range 0001-01-01 through 9999-12-31.

In Date primitive types, the initial value of zero (0) is not valid and can be distinguished from a valid (or set) value or an invalid date (set by user code).

In Time primitive types, the initial value of zero (0) is a valid time of 00:00:00 and cannot be distinguished from a set value. An invalid time can be set by user code.

The following is used to map values from JADE to the SQL Server 2000 and SQL Server 2005 DATETIME data type.

JADE Primitive Type	JADE Value	SQL Server 2000 and 2005 DATETIME Data Type
Date	0	Null column
	InvalidDate	Null column
	Valid SQL Server date	ValidDate, 00:00:00
	Invalid SQL Server date	Null column with message to log
Time	ValidTime (including 0)	TodaysDate if SetSQLDateForJadeTime=<default>
		1/01/1900 if SetSQLDateForJadeTime=<basedate>
	InvalidTime	Null column
TimeStamp	SQLServerValidDate, ValidTime (including 0)	Date, Time
	Invalid SQL Server date, ValidTime	Null column with message to log
	ValidDate, InvalidTime	ValidDate, 00:00:00
	InvalidDate, any Time	Null column
	0, InvalidTime	Null column
	0, ValidTime (including 0)	TodaysDate, ValidTime if SetZeroTimeStamp=<default>
		1/01/1900, ValidTime if SetZeroTimeStamp=<basedate>
		Null column if SetZeroTimeStamp=<null> and time=0

The following table defines the available mappings of JADE types to SQL Server 2008 and higher DATE and TIME data types that differ from the SQL Server 2000 and 2005 mappings listed in the previous table.

JADE Primitive Type	JADE Value	SQL Server 2008 and Higher DATE and TIME Data Types
Date	Valid SQL Server Date	ValidDate
	Invalid SQL Server Date	Null column with message to log (SQL Server date limits)
Time	ValidTime (including 0)	ValidTime