CODEWITHSUNDEEP
pythonsql-serverdataframe
CGarden
CGarden

Reputation: 347

Unable to amend dataframe column format from YYYY-MM-DD to DD/MM/YYYY using Python

Struggling with this one. I have pulled a date (YYYY-MM-DD) format field from sql server into a data frame using Python (data type now object). I would like to amend this column in the data frame to DD/MM/YYYY (I don't mind if the data type becomes a string), but I can't get it to work.

I tried:

df['Policy Date'] = pd.to_datetime(df['Policy Date'], format='%d%m%Y')

But got the below error:

ValueError: time data '2021-04-01' does not match format '%d%m%Y' (match)

I tried amending it first in the sql server script that pulls the data into the data frame:

convert(char(10), [Policy Date], 103)

Python/sql is giving me the error:

DataError: ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLFetch)')

Edit:

df['Policy Date'] = pd.to_datetime(df['Policy Date'], format='%YYYY-%mm-%dd')
df['Policy Date'] = df['Policy Date'].apply(lambda x: x.strftime('%dd/%mm/%YYYY'))

Gives me:

ValueError: time data '2021-04-01' does not match format '%YYYY-%mm-%dd' (match)

Upvotes: 0

Views: 161

Answers (1)

Enrique GO
Enrique GO

Reputation: 53

In your line of code

df['Policy Date'] = pd.to_datetime(df['Policy Date'], format='%d%m%Y')

the format parameter specifies the current format of the object type column you want transform to datetime type. Then this parameter's value should be '%Y-%m-%d'.

After having a datetime type column you can change it's format to the one you need by using

import datetime 

df['Policy Date'] = df['Policy Date'].apply(lambda x: x.strftime('%d/%m/%Y'))

Upvotes: 1

Related Questions