Dates used in query expressions

Dates can be specified in query expressions using the time() operator.

The time() operator can accept either absolute dates or relative dates. Such date strings are processed on the Rational® Synergy server using the locale and time zone of the server, not of the client. Relative dates use the time zone of the server and meaning of 00:00:00 for the start of the day. When the server machine is using a different time zone from the client machine, which can cause unexpected results, especially for relative dates.

The time() operator accepts any of the following formats:

  1. 'YYYY/MM/DD hh:mm:ss'. This format is accepted in all server locales. However, since it does not explicitly specify a time zone, times are processed in the time zone of the server. For example,'2009/03/12 15:12:33' means 12th March 2009 at 15:12:33 in the time zone of the server.
  2. Rational Synergy tries several formats, most of which are specific to the United States conventions in language, punctuation, and ordering. For example, ' Thu May 2 12:15' means 2nd May in the current year at 12:15 in the time zone of the server. 'April 10 1998' means 10th April 1998 at 00:00:00 in the time zone of the server.
  3. ISO8601 format. Date/time strings used in queries can be written in the ISO 8601 format of '2006-08-21 T09:12:15-0100' using the following parameters:

    The full four-digit year number must be supplied.

    The month, day, hour, minute, and seconds fields must be present and must be exactly two digits, with a leading zero if necessary.

    The year, month, and day fields must be separated by a single dash (-).

    The date and time must be separated by a single uppercase T.

    The time fields must be separated by a single colon (:).

    The time zone field must be present, and can be either a single uppercase Z for UTC (GMT) time, or a plus (+) or minus (-) character followed by an offset from UTC.

    The offset from UTC is in hours (two digits) and minutes (two digits); the hours and minutes might be separated by an optional colon (:) separator. Thus, -0500 and -05:00 are both valid time zone fields.

  4. Relative times of '-d:h:m:s' or '+d:h:m:s'. Some examples are shown below.

    '-0:0:0:0' means the start of today in the time zone of the server

    '+0:0:0:0' means midnight tonight in the time zone of the server

    '-1:18:0:0' means 6 A.M on the day before yesterday in the time zone of the server

    '-2:0:0:0' means the start of day before yesterday in the time zone of the server

    '-2:8:0:0' means 4 P.M. three days ago in the time zone of the server

There is an advantage to using the ISO8601 format for absolute dates. When the time zone is explicitly specified, the date is explicitly defined so that it is not dependent on the time zone or locale of the server.


Feedback