Skip to main content


datatype class %Library.DateTime extends %Library.TimeStamp


The %Library.DateTime datatype used mainly for T-SQL migrations and maps datetime/smalldatetime behavior to the %TimeStamp datatype. %DateTime is the same as %TimeStamp (is a sub-class or %TimeStamp) with extra logic in the DisplayToLogical and OdbcToLogical methods to handle imprecise datetime input T-SQL applications are accustomed to.

The formats supported for %DateTime can be broken into date formats and time formats. Date formats can be further broken into alphabetic formats and numeric formats.

Display and Odbc formats for alphabetic date values that are supported are as follows:

	Apr[il] [15][,] 1996	
	Apr[il] 15[,] [19]96 	
	Apr[il] 1996 [15]	
	[15] Apr[il][,] 1996 
	15 Apr[il][,][19]96 
	15 [19]96 apr[il]
	[15] 1996 apr[il]	
	1996 APR[IL] [15]	
	1996 [15] APR[IL]	

If you specify only the last two digits of the year, values less than the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than or equal to the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2050 (default), 25 is interpreted as 2025 and 50 is interpreted as 1950. To avoid any ambiguity. use four-digit years. If the day is missing, the first day of the month is supplied.

The %Library.DateTime datatype also allows you to specify date data with a numeric month specified. For example, 9/13/98 represents the thirteenth day of September, 1998. When using numeric date format, specify the month, day, and year in a string with slash marks (/), hyphens (-), or periods (.) as separators.

This string must appear in the following form:

	number separator number separator number [time] [time]
	These numeric formats are valid:
	[0]4/15/[19]96 -- (mdy)
	[0]4-15-[19]96 -- (mdy)
	[0]4.15.[19]96 -- (mdy)
	[04]/[19]96/15 -- (myd)
	15/[0]4/[19]96 -- (dmy)
	15/[19]96/[0]4 -- (dym)
	[19]96/15/[0]4 -- (ydm)
	[19]96/[04]/15 -- (ymd)

The default order for the date is mdy. You can change the date order with the DATEFORMAT parameter. The setting for the DATEFORMAT parameter determines how date values are interpreted. If the order does not match the setting, the values are not interpreted as dates (because they are invalid dates), or the values are misinterpreted. For example, 11/10/09 can be interpreted as one of six dates, depending on the DATEFORMAT parameter setting: mdy, dmy, ymd, ydm, myd, and dym

%DateTime recognizes the following formats for time data in Display and Odbc mode:

	4 PM

You can specify a suffix of AM or PM to indicate if the time value is before or after 12 noon. The case of AM or PM is ignored. Hours can be specified using either a 12-hour or 24-hour clock.

This is how the hour values are interpreted:

- The hour value of 0 represents the hour after midnight (AM), regardless of whether or not you specify AM. You cannot specify PM when the hour equals 0.

- Hour values from 1 through 11 represent the hours before noon if neither AM nor PM is specified. They also represent the hours before noon when AM is specified. They represent hours after noon if PM is specified.

- The hour value 12 represents the hour that starts at noon if neither AM nor PM is specified. If AM is specified, it represents the hour that starts at midnight. If PM is specified, it represents the hour that starts at noon. For example: 12:01 is 1 minute after noon, as is 12:01 PM, while 12:01 AM is 1 minute after midnight. Specifying 12:01 AM is the same as specifying 00:01 or 00:01 AM.

- Hour values from 13 through 23 represents hours after noon if AM or PM is specified. They also represent the hours after noon when PM is specified. You cannot specify AM when the hour value is from 13 through 23.

- An hour value of 24 is not valid, use 12:00 AM or 00:00 to represent midnight.

Milliseconds can be preceded by either a colon (:) or a period (.). If preceded by a colon, the number means thousandths-of-a-second. If preceded by a period, a single digit means tenths-of-a-second, two digits mean hundredths-of-a-second, and three digits mean thousandths-of-a-second. For example, 12:30:20:1 indicates twenty and one-thousandth seconds past 12:30; 12:30:20.1 indicates twenty and one-tenth seconds past 12:30.

Method Inventory


parameter DATEFORMAT = mdy;
Order of the date parts when a numeric date format is specified for the Display or Odbc input value. Valid parameters are mdy, dmy, ymd, ydm, myd, and dym. The default DATEFORMAT is mdy.


classmethod DisplayToLogical(%val As %String) as %TimeStamp
Converts the input value %val, which represents a Display timestamp value, to YYYY-MM-DD HH:MM:SS[.nnnnnnnnn] format. The Logical value and Display values are the same unless there are VALUELIST and DISPLAYLIST parameters specified.

Returns the value of the input string %val as a Logical timestamp value (YYYY-MM-DD HH:MM:SS[.nnnnnnnnn] format).

classmethod IsValid(%val As %RawString) as %Status
Tests if the logical value %val, which represents a timestamp value in YYYY-MM-DD HH:MM:SS[.nnnnnnnn] format, is valid. The validation is based on the class parameter settings used for the class attribute this data type is associated with. In this case, MAXVAL and MINVAL.
classmethod Normalize(%val As %RawString) as %TimeStamp
Transforms TSQL datetime values into timestamp. Date values are converted to Timestamp values with a time of 00:00:00.
classmethod OdbcToLogical(%val As %String) as %TimeStamp
Converts %val, which represents either a Timestamp in ODBC format, or a Date in ODBC format, into a logical Timestamp value.

Returns the logical Timestamp value of the ODBC Timestamp string %val.

Inherited Members

Inherited Methods

FeedbackOpens in a new tab