Problem Note 66871: Dates read in from a Google BigQuery database might show a difference from what is stored in the database
 |  |  |  |  |
When you read in a Google BigQuery table that contains a variable that is defined as a date data type, the date in SAS® might be different from what was stored in the database. This issue occurs in the following cases:
- Dates between 29FEB4000 and 28FEB8000 show a one-day difference.
- Dates after 28FEB8000 show a two-day difference.
- Dates with a year between 01JAN0100 and 31DEC1582 show ******* when displayed and are internally stored as a non-missing value.
- Dates with a year < 100 show ******* when displayed and are internally stored as a date that does not correspond to a date with years 2001-2099.
This issue occurs when you use BULKUNLOAD=YES to read in the table. The issue arises because SAS does not consider years divisible by 4000 as being leap years and the BigQuery database does.
The only workaround is to use BULKUNLOAD=NO to read in the table when you have dates with a year 4000 or later or outside of the range of dates that SAS supports.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to Google BigQuery (on SAS Viya) | Linux for x64 | 2020.0.4 | 2020.1.2 | Viya | Viya |
| SAS System | SAS/ACCESS Interface to Google BigQuery | Linux for x64 | 9.42 | 9.42 | 9.4 TS1M7 | 9.4 TS1M8 |
A fix for this issue for SAS/ACCESS Interface to Google BigQuery 9.42 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/K2L.html#66871A fix for this issue for SAS/ACCESS Interface to Google BigQuery 9.41 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/H5A.html#66871| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2020-12-07 15:24:00 |
| Date Created: | 2020-10-30 08:48:26 |