OP 14 functionality (including the Reports functionality that uses SQL) processes date-related information according two types of data (DATE and DATETIME). In processing both of these types of date-related information, OP 14 adheres to several sets of logic. It is important to understand the differences between these two types of date formats and the logic that OP 14 follows in processing date-related information.
This article details the date-related functionality in OP 14 and how that impacts the OP 14 end user when generating reports.
|If you have generated reports that have resulted in errors or inaccurate data, the cause may be an incorrect use or understanding of the date-related functionality in OP 14.|
Basic Concepts Regarding Dates
1. OP 14 stores date-related information in one of two formats (the spelling and capitalization reflect the format that the OP 14 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 14 (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 14 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. 9:15 AM is newer than 12:34 PM from the previous date.
6/3/2018 09:15:03 AM is not Greater Than 6/3/2018 12:34:38 PM. 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 14 and SQL compare DATE and DATETIME information, the software code assumes midnight as the time for a date without a timestamp. Here are some examples of how this functionality in OP 14 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 14 and SQL define 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 14 and SQL use 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 may 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 14 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 14 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.