I have a pandas dataframe which includes a column named "date" and its type is datetime64[ns]. some date values are missing and therefore are filled in with value NaT which is np.datetime64('NaT'). when I import it to T-SQL database using fts.fast_to_sql() method from fast_to_sql library, I see in SSMS that the NaT values are stored with zero datetime as "0001-01-01 00:00:00.0000000". I want T-SQL to treat those missing dates as NULL, because in my table I have allowed Nulls and when I change the zero times to NULL, SSMS changes the record, So I assume that it is possible.
dataframe=pd.DataFrame({'date': pd.Series(["2022-01-01 00:00:00",np.datetime64('NaT'),],
dtype='datetime64[ns]')})
driver='ODBC Driver 17 for SQL Server'
server = 'desktop-curtnl0\sqlexpress'
Trusted_Connection='yes'
username = ''
password = ''
creds = dict(driver=driver,
server=server,
database_name=database_name,
Trusted_Connection=Trusted_Connection,
username=username,
password=password)
connection='driver={%s};server=%s;database=%s;Trusted_Connection=%s;uid=%s;pwd=%s' %\
( creds['driver'], creds['server'], creds['database_name'],
creds['Trusted_Connection'], creds['username'] , creds['password'] )
conn = pyodbc.connect(connection)
create_statement = fts.fast_to_sql(df=dataframe,
name="test",
conn=conn,
if_exists="replace",
temp=False)
conn.commit()
conn.close()
