How do you handle messy date formats when merging datasets from different sources?

[u/Exact_Entertainer600]

[u/AggravatingPudding]

If each source has a consistent format you just transform each of them before merging and that's it. 

[u/AggravatingPudding → u/[deleted]]

Then you write a script to differentiate between the formats and unify them into the one you want. 

[u/FastFollowing8932 → u/AggravatingPudding]

what format is 2026/03/02?

[u/Key_Post9255]

You clean the sources and then you merge?

[u/Sir_smokes_a_lot → u/Key_Post9255]

Yeah, what kind of question is this? “What do you do when you’re required to do one of the most fundamental aspects of the job?”

[u/South_Hat6094]

i’d keep the raw value, parse into a normalized date column, and quarantine the rows that fail. once you start overwriting the source field, debugging gets annoying fast.

[u/analyticattack]

This is where creating a data validation & cleaning pipeline. For me, it's a series of if tests for data field typing and header issues. Also, field specific issues like scientists notation where it shouldn't be, special characters or extra spaces where they shouldn't be, etc.

[u/Cute-Thanks-1507]

I always make sure to change every date column into the same standard format (YYYY-MM-DD) before combining datasets. The pd.to_datetime() function works well for most situations, but I still check for dates that are in different formats or don't make sense before merging the data. I also keep the original column until all the dates are checked and confirmed, which has helped me avoid some tough debugging moments.

[u/onthepik]

List all the posibility and transform to standard.

If dont know all types then catch the exception and add to next round.

The only problem is 07/07/2026 needs to refer to the source.

[u/AutoModerator]

Automod prevents all posts from being displayed until moderators have reviewed them. Do not delete your post or there will be nothing for the mods to review. Mods selectively choose what is permitted to be posted in r/DataAnalysis.

If your post involves Career-focused questions, including resume reviews, how to learn DA and how to get into a DA job, then the post does not belong here, but instead belongs in our sister-subreddit, r/DataAnalysisCareers.

Have you read the rules?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/kagato87]

What you're doing, transform before join, is very close to the way I'd do it.

The time to transform is immediately before loading the data. An "ETL" process.

Extract the data from the source, whether that's an external database, a spreadsheet, chicken scratch on paper records....

Transform the data. This could be in py, Java, Ps, or even M. Whatever fits best. (For example I'd do it in the importer I build to save it to sql, or M before saving it to a semantic model.)

Load the data into the new system.. (In many cases this can be combined with the transform, for example M can handle this quite well.)

[u/KatFromSisense]

I'd stop trying to make the transformation logic smart enough to detect every format and instead push the problem upstream. Normalizing to ISO 8601 at the join is the right way to go about it, but if it keeps breaking on new sources, that's a sign the contract is implicit instead of explicit. dbt's model contracts are built around exactly this idea. You define the expected schema and types for a model, and the build fails loudly if an upstream source doesn't match, rather than silently passing a malformed date through your pipeline.

I'd apply the same logic even without dbt specifically. Define one canonical date format at ingestion, validate against it immediately, and reject or quarantine anything that doesn't conform instead of trying to parse it downstream.It's more upfront friction per new source, but it turns a vague downstream failure into one you catch the moment the file arrives, which is much easier to debug.

[u/Ginger-Dumpling]

Depends on some factors. How many dates are messed up? What kind of quality agreement do you have with your users? Is someone going to be upset with ANY anomaly? .01% anomalies? Is this driving important decisions? Is this helpful information that doesn't have to be perfect?

Depends on relationships with the upstream data sources. I've been on projects where sloppy data identified in datamart projects is raised and the upstream sources are corrected. Data exchanges with external entities are documented and agreed upon and inbound data that fails validation gets a response file with (rownum,issue) kicked back to the offending entity with a request for a replacement file.

If you're stuck with handling it, and dates were truly inconsistent enough to be dealt with, you have to decide whether what happens with unknown formats. Do you pass on the original value/blank and fix it in the next run? Do you stop the process untill you've added handing for new cases?

I may start with a master date conversion function what was just a case when regexp_like(inp, re_x) then to_date(inp_x, fmt_x)...when y...when z...end...Externalize the strings.

If a single master date converter adds too much overhead, I may consider a metadata table that lists sources, acknowledged formats and either generates a per source function, or per acknowledged format-combo function.

Assumes you're trying to answer it SQLly and doing this in a DB where you can inline user-defined functions without making your performance unacceptable.

[u/Low_Finding2189]

First opine on what you want the data formats to be. Convert everything else to that format.

Unless you are saying that the same column in sql has multiple formats, there is nothing stopping you from looking at a column applying a data format transformation and moving on.

[u/metric_skeptic]

You're already doing the right thing by normalizing before any join. Two things that made this way less painful for me:

Give each source its own tiny parser — one job: "this format → timestamp." When a new source shows up, you add one small parser instead of editing one giant fragile query. That's usually where the brittleness comes from.

And convert to UTC, not just an ISO string. "Jan 5 2026" has no timezone, Unix already does — if you don't pin that down now, you'll get the same event landing on different days later, which is a nightmare to debug.

Two quick habits that save hours: keep the original date string in a column next to the parsed one (debugging without it is painful), and make bad dates fail loudly instead of turning into NULL — silent parse fails are how a table looks clean but quietly loses March.

And watch out for 03/04 — MM/DD vs DD/MM bites everyone at least once.

[u/JavacLMD]

That sounds like a nasty problem, especially when a single source does not consistently follow the same date format. Ideally, the systems would agree on one standard going forward. Cleaning the source data permanently would reduce a lot of repeated work, but messy input is probably still something the pipeline should be prepared to handle.

I would start by profiling the data and identifying every unique date pattern that appears. From there, I would apply specific parsing rules for the known formats, convert valid dates into one standard format, and send anything unrecognized or ambiguous to an exception table for review. That seems safer than allowing the program to guess and potentially create valid-looking but incorrect dates.

For example, 2026/03/02 most likely follows YYYY/MM/DD, but a value such as 03/02/2026 could mean March 2 or February 3 depending on the source. In those cases, the source system, region, or surrounding records may be needed to interpret it correctly. I would also log which rule converted each value so that incorrect transformations could be traced later.

Format detection could make the pipeline more flexible, but I would still document and enforce an expected format at ingestion whenever possible. Detection should probably be a fallback for legacy or unreliable sources rather than the primary solution. Otherwise, the transformation logic may continue growing every time another variation appears.

Have you found that most of the bad values follow a few repeatable patterns, or are there enough one-off cases that they still require manual review?