Description
Code Sample, a copy-pastable example if possible
import pandas as pd
import datetime
d0 = datetime.date(1970, 1, 1) # python epoch
d1 = datetime.date(9999, 12, 31) # oft-used max-date in SAS / DB2
for days in range(106752, (d1 - d0).days): # 2932896 is the number of days
try:
data = {'days_from_python_epoch': [days]}
df = pd.DataFrame(data)
df['date_as_date'] = pd.to_datetime(df['days_from_python_epoch'], unit='d', origin='1970-01-01')
except pd._libs.tslibs.np_datetime.OutOfBoundsDatetime as e:
print(f'exception with {days}')
z = d0 + datetime.timedelta(days=days -1)
print(f'looks like {days} -1 -> {z} is the max possible') # '2262-04-11T00:00:00.000000000'] is the max possible
raise e # 106752 throws exception
Problem description
This causes import of some SAS datasets to fail, and potentially imports/conversions from other sources (such as DB2) where an arbitrarily high date (often 9999-12-31) is used in datawarehousing to indicate currently valid rows with a to_date=9999-12-31
see related issue #20927 - reposted here as the issue is more generic than just SAS dataset imports
and maybe #15836
I'm pretty sure the root cause is because to_datetime() converts via nanoseconds ( in _libs.tslibs.timedeltas.cast_from_unit ) and very large dates in nanoseconds do not fit into int64 space.
Expected Output
correctly parsed dates up to and including 9999-12-31
Output of pd.show_versions()
pandas: 0.23.1
pytest: 3.6.2
pip: 10.0.1
setuptools: 39.2.0
Cython: 0.28.3
numpy: 1.14.5
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: 6.4.0
sphinx: 1.7.5
patsy: 0.5.0
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: 1.2.1
tables: 3.4.4
numexpr: 2.6.5
feather: None
matplotlib: 2.2.2
openpyxl: 2.5.4
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: 1.0.5
lxml: 4.2.2
bs4: 4.6.0
html5lib: 1.0.1
sqlalchemy: 1.2.8
pymysql: None
psycopg2: 2.7.5 (dt dec pq3 ext lo64)
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None