Problem Note 67689: An error might occur when you create a new BigQuery table using a PROC SORT of a BigQuery table with a date equal to 31DEC9999
 |  |  |  |  |
When you try to use the SORT procedure on a Google BigQuery table, an error might occur when the following is true:
- The BigQuery table contains a DATE, DATETIME, or TIMESTAMP data type and at least one of the variables contains a date equal to 31DEC9999.
- The BULKUNLOAD=YES LIBNAME or data set option is being used.
- The OUT= option for the PROC SORT statement also references a BigQuery table.
If the BULKLOAD=NO LIBNAME or data set option is being used, the error might look similar to the following:
ERROR: During insert: Error: JSON: error calling MarshalJSON for type *bigquery.TableDataInsertAllRequest: JSON: error calling MarshalJSON for type *bigquery.TableDataInsertAllRequestRows: JSON: error calling MarshalJSON for type time.Time: Time.MarshalJSON: year outside of range [0,9999]
If the BULKLOAD=YES LIBNAME or data set option is being used, the error might look similar to the following:
NOTE: The data set DBLIB.xxxx has 4 observations and 3 variables.EERROR: Error while executing bulk load: {Location: "query"; Message: "Cannot return an invalid timestamp value of -62167132800000000 microseconds relative to the UNIX epoch. The range of valid timestamp values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]; error in writing field dt"; Reason: "invalidQuery"}
With BULKLOAD=YES, the note incorrectly indicates that some rows were successfully inserted into the table.
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 | SAS/ACCESS Interface to Google BigQuery | Linux for x64 | 9.41 | 9.4 TS1M6 | ||
| SAS System | SAS/ACCESS Interface to Google BigQuery (on SAS Viya) | Linux for x64 | 2020.1 | 2020.1.2 | Viya | Viya |
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#67689| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2021-05-06 10:58:55 |
| Date Created: | 2021-03-30 18:27:38 |