Problem Note 59871: When using a numeric value instead of a date constant to subset a DBMS table, unexpected results or performance problems occur
 |  |  |  |  |
Incorrect results or a performance problem might occur when the following conditions are true:
- You are accessing at least one database management system (DBMS) table via a LIBNAME statement.
- Your SQL query contains a WHERE clause that is comparing a date variable using the BETWEEN operator.
- The date variable is being compared to an internal SAS® value for a date variable (such as the number of days since 01JAN1960) instead of using a SAS date constant.
- Your SQL query triggers implicit pass-through.
Incorrect results occur when a DBMS allows the comparison of a date variable with a number, and the number is not interpreted as the intended date.
Performance problems occur when a DBMS does not allow the comparison of a date variable and a number because the data types are not compatible. In this scenario, the query is partially processed by the DBMS and partially processed by SAS. This performance problem is especially apparent if the SQL query contains a join of two or more DBMS tables, because the join and the subsetting are done in SAS rather than in the DBMS.
The workaround is to use a SAS date constant instead of the number.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | Base SAS | z/OS | 9.4_M4 | 9.4 TS1M4 | ||
| z/OS 64-bit | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft® Windows® for x64 | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows 8 Enterprise 32-bit | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows 8 Enterprise x64 | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows 8 Pro 32-bit | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows 8 Pro x64 | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows 8.1 Enterprise x64 | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows 8.1 Pro 32-bit | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows 8.1 Pro x64 | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows 10 | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows Server 2008 | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows Server 2008 R2 | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows Server 2008 for x64 | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows Server 2012 Datacenter | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows Server 2012 R2 Datacenter | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows Server 2012 R2 Std | 9.4_M4 | 9.4 TS1M4 | ||||
| Microsoft Windows Server 2012 Std | 9.4_M4 | 9.4 TS1M4 | ||||
| Windows 7 Enterprise 32 bit | 9.4_M4 | 9.4 TS1M4 | ||||
| Windows 7 Enterprise x64 | 9.4_M4 | 9.4 TS1M4 | ||||
| Windows 7 Home Premium 32 bit | 9.4_M4 | 9.4 TS1M4 | ||||
| Windows 7 Home Premium x64 | 9.4_M4 | 9.4 TS1M4 | ||||
| Windows 7 Professional 32 bit | 9.4_M4 | 9.4 TS1M4 | ||||
| Windows 7 Professional x64 | 9.4_M4 | 9.4 TS1M4 | ||||
| Windows 7 Ultimate 32 bit | 9.4_M4 | 9.4 TS1M4 | ||||
| Windows 7 Ultimate x64 | 9.4_M4 | 9.4 TS1M4 | ||||
| 64-bit Enabled AIX | 9.4_M4 | 9.4 TS1M4 | ||||
| 64-bit Enabled Solaris | 9.4_M4 | 9.4 TS1M4 | ||||
| HP-UX IPF | 9.4_M4 | 9.4 TS1M4 | ||||
| Linux for x64 | 9.4_M4 | 9.4 TS1M4 | ||||
| Solaris for x64 | 9.4_M4 | 9.4 TS1M4 | ||||