1

The date of Jan 01, 1901 converts to Jan 01, 2001 which is wrong. How is this done in Databricks?

%sql
Select '01-Jan-01' badDate, to_date('01-Jan-01','dd-MMM-yy') as date2, date_format('01-Jan-01','d-MMM-yy'),,date_format('01-Jan-01','dd-MM-yy'),date_format('01-01-01','dd-MM-yy');
Jamie
  • 437
  • 4
  • 15

2 Answers2

0

Its unclear what you're asking but your date format string is incorrectly formated for one. You have dd-MMM-yy when it should be dd-MM-yy

johnFisher1978
  • 13
  • 4
  • Can you show an example? I think the MMM indicates we are using the month abbreviation rather than an actual numeric value for the month. Perhaps I should add the CSV file has the date written as '01-Jan-01' and the date needs to be converted to something like '1901-01-01' or more simply - it needs to be interpreted as a date and not a string. – Jamie Jan 20 '23 at 15:24
0

Just a thought - since it is Databricks. Use a Python function to convert it using a cutoff date of '01-01-'+ year(date)

cutoff_date = pd.to_datetime('01-01-2023')
df.loc[df.date > cutoff_date, 'date'] -= pd.DateOffset(years=100)

Possible solution Easy way to fix wrong year (y2k bug) using pandas

jamiel22
  • 117
  • 6
  • That could work if the cutoff date could list as '01-01-2000', the Y2K date, but it isn't functional unless there can be certainty that the date will always be from the 20th century and that is not an option. – Jamie Jan 20 '23 at 15:51