OP Reports Tutorials: Working with DatesModule 6, Lesson 3
Working with Dates
To use today’s date in OP Reports SQL, use the construction
CAST(‘NOW’ AS DATE)
For example, to exclude children under age 90 days from a report, you can use the SQL phrase
WHERE (CAST(‘NOW’ AS DATE) – REGISTER.BIRTHDAT) > 90
…in other words, where today’s date is more than 90 days in the future from the child’s date of birth.
Dates in the OP database are stored in three formats, depending on how they are going to be used. Take, for example, Thursday, April 6, 2017. This date might be stored as:
- a date (e.g. 04/06/2017)
- a date/time (e.g. 04/06/2017 00:00:00 means midnight on April 6)
- an integer (e.g. 42831). This 5-digit integer (also called the Microsoft Timestamp) is the number of days elapsed since January 1, 1900.
The SCHEDULE table contains date data in all three of these formats:
- APPOINTMENT_DATE and VERIFICATION_DATE are dates
- ARRIVAL_TIME and CREATE_DATETIME are date-times
- DATE1 is an integer
Knowing how the date is stored in OP may affect how you search for it.
For example, if you want to look at a financial report for the entire month of March 2017, it would be reasonable to set parameters like:
SELECT PATNO, DATE1, PAYMENT FROM ARCHIVE_TRANSACTIONS WHERE ARCHIVE_TRANSACTIONS.DATE1 BETWEEN ’03/01/2017′ AND ’03/31/2017′
The BETWEEN term includes the parameters. That is, if you’re looking for dates BETWEEN 3/1 and 3/31, the first and last dates are included. Because ARCHIVE_TRANSACTIONS.DATE1 is a date (rather than a date-time — see below), the above query will get payments on 3/1/17, 3/31/17, and all the dates in between.
However, search for date-time dates differently. If you are looking for SCHEDULE.ARRIVALTIME values during the month of March 2017, you might consider searching like:
WHERE SCHEDULE.ARRIVALTIME BETWEEN ’03/01/2017′ AND ’03/31/2017′
However, because date-time stamps default to midnight unless otherwise specified, this is the same as:
WHERE SCHEDULE.ARRIVALTIME BETWEEN ’03/01/2017 00:00:00′ AND ’03/31/2017 00:00:00′
If the date range excludes dates after midnight of 3/31, it will actually exclude most of 3/31. Therefore, for date-time fields, the correct way to limit results to a calendar month would be:
WHERE SCHEDULE.ARRIVALTIME BETWEEN ’03/01/2017 00:00:00′ AND ’04/01/2017 00:00:00′
Similarly, to only get afternoon arrival times on 04/14/2017, use:
WHERE SCHEDULE.ARRIVALTIME BETWEEN ’04/14/2017 12:00:00′ AND ’04/15/2017 00:00:00′