For an explanation of Queries, please see LabVantage Queries. This document explains how dates are treated when used as criteria for displaying data in the LabVantage user interface.

Some standard queries are based on or include a date or a date range. While date values stored in the database include date (calendar day) and time, a "date" for query purposes is just the calendar day not including time. More specifically, for conceptual use in queries, a date effectively represents a calendar day from 00:00:00 (midnight) through 23:59:59. For the general class of queries involving dates, user-supplied argument values may or may not include time. Included time will be stripped, so a user-supplied date value effectively will refer to midnight of that calendar day in the user's time zone. To avoid ambiguity in this document, "date" will refer to a calendar day without time and "datetime" will refer to the date with time included.

For consistency and simplicity, date handling in queries will always refer to a date data type value representing midnight of a calendar day in the user's time zone, converted to system time zone.

Datetime values in the database are stored using the database's time zone. In the context of the user interface, people normally assume date and datetime values reflect the user's time zone. The users might not operate in the same time zone as the database. Thus, 23:00 on December 31, 1999 may be 06:00 on January 1, 2000 for different users. As such, queries involving stored datetime values must take into account user time zones for proper comparison. For use in queries involving dates, this datetime as 00:00:00 (midnight) in the user's time zone must be converted to a datetime in the system time zone.

For consistency and simplicity, date (without time) handling in queries will always refer to a date data type value representing midnight of a calendar day in the user's time zone, converted to system time zone. The "beginning" of a day for comparison purposes will be this 00:00:00 midnight value. The "end" of that day is normally up to and including 23:59:59. When a person selects an end date of today (again, not selecting a time), the assumption is the query will show results for everything up to and including today. To handle this, LabVantage will strip any time making the time value midnight. Next, add a day to the user end-date selection before issuing a query which will include everything less than 00:00:00 the next day in that user's time zone.

Beyond ensuring consistency, the primary benefit of using date substitution tokens is that both the complexity described above, and database-specific syntax are hidden, eliminating need for Oracle and MSS versions or queries that do not have other database-specific syntax. All tokens are replaced in the built query with a string returning a date data type that represents 00:00:00 (midnight) in the user's time zone, converted to system time zone. When arguments are involved, the returned string will include the argument tokens (per queryarg.argvalue) such that proper substitution with actual supplied user date value(s) will also be subsequently applied

No argument – "today" –  [%usertoday%]

• [%usertoday%] will be replaced in the built query with a string value, returning a date data type representing 00:00:00 (midnight) for the user's current day in the user's time zone, converted to system time zone
• Examples:
  • (today)    duedt >= [%usertoday%] AND duedt < [%usertoday%] + 1
  • (future)   duedt > [%usertoday%] + 1
  • (past)     duedt < [@usertoday%]

• arginto argument must be [date]
• [%adjdate%] will be replaced in the built query with a string value, including [date], returning a date data type representing 00:00:00 according to [date] with conversion to system time zone
• If no [date] value is provided, server date is used
• Example use: (on given date)   duedt >= [%adjdate%] AND duedt < [%adjdate%] + 1
• Query definition must include argument [date] in queryarg table as the passed input date value 
  • [%adjdate%] token uses [date] internally

• arginto arguments must be [startdate] and [enddate]
• [%adjstartdate%] and [%adjenddate%] will be replaced in the built query with string values, including [startdate] and [enddate], returning a date data types representing 00:00:00 according to [startdate] and [enddate], respectively, with conversion to system time zone
• If no [startdate] value is provided, 1/1/1900 is used
• If no [enddate] value is provided, 1/1/2100 is used
• Example: (between given dates)   duedt >= [%adjstartdate%] AND duedt < [%adjenddate%] + 1
• Query definition must include argument [startdate] and [enddate] in queryarg table as the passed input date range values
  • [%adjstartdate%] and [%adjenddate%] use [startdate] and [enddate] internally

 NOTE: As of LabVantage 8.5.0, These substitution tokens are built into the database API, but are not currently implemented in the application API. Defined queries using these tokens must be query-based RSets

Substitution Token/Defined Argument Cross Reference	Top

Date substitution tokens translate to specific defined arguments (per arginto in queryarg)

• The Core LabVantage queries are standardized to all use these values as in cross reference table below
• Single date queries mostly used [date], range queries mostly already used [startdate]/[enddate] 
• Substitution tokens currently allow up to 2 date ranges and up to 3 single dates

Oracle Format Masks and Date Tokens	Top

Date queries with argument values currently all only use format mask: "dd/mm'yyyy hh:mi:ss am". Accordingly, RSet date substitution tokens all use this format, implying all supplied argument values for dates are expected in this format. While query definitions can be modified in query definitions should a different format be needed, users cannot directly adjust the formats in the database API. Should a different format be necessary, the Oracle database API implements a global property, propertyid = RSetQueryDateFormat, that can be used to specify a different format mask. This propertyvalue must be a valid Oracle date format mask, and values passed to query-based RSets must then be in this format. As with all RSet global properties, any changes require app server restart since the settings are cached in the Oracle sessions. NOTE: For MSS, configurable date format mask does not seem to be applicable at this time, so no override is available.