Version 21.0

OP functionality (including the Reports functionality that uses SQL) processes date-related information according two types of data (DATE and DATETIME). It is important to understand the differences between these two types of date formats and the logic that OP follows in processing date-related information.

This article details the date-related functionality in OP and how that impacts the OP end user when generating reports.

Basic Concepts Regarding Dates

1. OP stores date-related information in one of two formats (the spelling and capitalization reflect the format that the OP database uses to process and store this information):

• DATE: The DATE format stores only the date, which is the month, day and year (for example, "5/3/2018").  
• DATETIME: The DATETIME format stores the date and time, which is the month, day, year, hour, minute, second, and AM or PM (for example, "5/3/2018 09:15:03 AM").

2. The software code in OP (including the SQL functionality) refers to DATE and DATETIME information that is more recent than other dates as Larger or Greater Than. Here are some examples of how the functionality in OP processes DATE and DATETIME information: 

• 6/3/2018 is greater than 5/3/2018.

• Dates such as 6/4/2018, 6/5/2018, 6/6/2018 are greater than 6/3/2018.

• Dates such as 6/3/2018, 6/4/2018, 6/5/2018 are greater than or equal to 6/3/2018.

• 6/3/2018 09:15:03 AM is greater than 6/2/2018 12:34:38 PM (because 9:15 AM is newer than 12:34 PM from the day prior.)

• 6/3/2018 09:15:03 AM is not greater than 6/3/2018 12:34:38 PM (because 9:15 AM is older than 12:34 PM on the same date.)

• DATETIMES such as 6/3/2018 on or after 9:15:03 AM, and any time on 6/4/2018, 6/5/2018, 6/6/2018 are greater than or equal to 6/3/2018 09:15:03 AM.

3. When OP's SQL compares DATE and DATETIME information, OP presumes that a date without a time is midnight. Here are some examples of how this functionality in OP processes based upon this assumption:

• 6/3/2018 is not equal to 6/3/2018 09:15:03 AM. (6/3/2018 is 6/3/2018 00:00:00 AM, which is not equal to 6/3/2018 09:15:03 AM, which is greater than 6/3/2018 00:00:00 AM).  

• 6/3/2018 09:15:03 AM is greater than 6/3/2018 (because 6/3/2018  09:15:03 AM is more recent than 6/3/2018 00:00:00 AM.)

4. In reference to date-related information, OP's SQL defines "between" to be greater than or equal to the first parameter and less than or equal to the second parameter.  Here are some examples that illustrate how OP's SQL uses "between" in processing date-related information:

• For the request, "Find the DOB between 6/3/2018 and 6/10/18," OP 14 and SQL use the logic, "Find DOB on or after 6/3/18 and on or before 6/10/18."

• For the request, "Find appointment date times between 6/3/18 and 6/10/18," OP 14 and SQL use the logic, "Find appointment date/times on or after midnight on 6/3/18 and on or before midnight on 6/10/18."

How to Use Date-related Information Correctly

Entering DATE parameters into a DATETIME field can lead to unexpected or inaccurate results. The following examples illustrate the date-related logic and what to be aware of when entering date-related information in reports. 

AC 04 Message Response Times

When the end user enters DATE information in a DATETIME field for AC 04 Message Response Times:

• The date range is not inclusive (that is, covering the beginning and ending days), because the Date range uses the DATE format and includes only the date (month, day, and year) while messages use the DATETIME format (the date and time).
  This means that a message received on 6/1/2018 at 9:39 AM logically falls after 6/1/2018 00:00:00 and will not appear on a date range that ends 6/1/2018.

• A date range of 6/1/2018 to 6/1/2018 will always process no results, because no messages are sent precisely at midnight (even though a practice might reasonably expect to see one day's worth of messages for that one day specified in the report).

DA&R Show Future Appointments / Exclude Patients with Appointments

When the end user enters DATE information in a DATETIME field for DA&R Show Future Appointments / Exclude Patients with Appointments reports:

• If you run this report, for example, at 9:00 AM on Tuesday, 6/11/18, patients who are on the schedule at 2:30 PM on Tuesday, 6/11/18 will not show up in the report as having a Future Appointment, because OP is programmed to report on Future Appointments starting with the day after the date the report is generated. This means that a report based on these parameters will only show patients with Future Appointments for the next day (Wednesday, 6/12/18) or later. This occurs because the SQL logic processes these date parameters as follows:
  • The 2:30 appointment date on Tuesday, 6/11/18 is greater than (that is, later than) the Appointment Date of 06/11/2018, because the scheduled appointment uses a DATE format and that begins at midnight (6/11/2018 00:00:00 AM).
  • If the end user runs the report at 9:30 AM,  the timestamp of the report will be: 06/11/2018 09:30:00 AM. When the end user runs the report, OP uses a DATETIME format (6/11/2018 00:00:00 AM).
  • The logic in this report looks for future appointments on the day after 06/11/2018 00:00:00 AM, which means it will only find future appointments scheduled for 06/12/2018 00:00:00 AM and later.