Reputation: 1677
Scenario: Following up from a previous question (Removing the timestamp from a datetime in pandas dataframe) I have a code that reads data from excel into a pandas dataframe and uses the command dataframe.to_sql to insert that data into a given SQL database.
Problem: Since my dates are retrieved in the american format (mm/dd/yyyy) and my database is in the international format (dd/mm/yyyy), I get a type error trying to upload my data to SQL.
What I already tried: I tried the suggestion given in the previous question and in that way the data changing runs, but I get the error when I try to upload to SQL.
Line that I am currently using:
fnl['Date'] = pd.to_datetime(fnl['Maturity'], errors='coerce')
which allows the code to run, but yields the problem in SQL.
I also tried:
fnl['Date'] = pd.to_datetime(fnl['Date'], format='%m/%d/%Y', errors:'coerce')
and
fnl['Date'] = pd.to_datetime(fnl['Date'], format='%m/%d/%Y')
Still with no success.
I previously changed all my encoding to utf-8 in the beginning of the code, so I can't see where the problem lies.
Question: How can I solve this problem?
Data example:
Date
1/15/2023
1/15/2023
6/30/2023
6/30/2023
8/1/2022
8/1/2022
7/25/2022
7/25/2022
7/19/2024
7/13/2022
7/13/2022
Upvotes: 2
Views: 474
Reputation: 403278
If your Date
column returns dates in international format, you'll want to reflect that when you're converting to datetime.
fnl['Date'] = pd.to_datetime(fnl['Date'], format='%m/%d/%Y', errors='coerce')
You'll need to specify the format in the source so that the conversion can be done properly.
If you want to convert your datetime back to a string in the %m/%d/%Y
format, use the .dt.strftime
function:
fnl['DateString'] = fnl['Date'].dt.strftime('%d/%m/%Y')
s = pd.to_datetime(df['Date'], format='%m/%d/%Y', errors='coerce')
s
0 2023-01-15
1 2023-01-15
2 2023-06-30
3 2023-06-30
4 2022-08-01
5 2022-08-01
6 2022-07-25
7 2022-07-25
8 2024-07-19
9 2022-07-13
10 2022-07-13
Name: Date, dtype: datetime64[ns]
s = s.dt.strftime('%d/%m/%Y')
s
0 15/01/2023
1 15/01/2023
2 30/06/2023
3 30/06/2023
4 01/08/2022
5 01/08/2022
6 25/07/2022
7 25/07/2022
8 19/07/2024
9 13/07/2022
10 13/07/2022
Name: Date, dtype: object
Upvotes: 2