One step back, two steps forward:⌗

Stop building on top off bad decisions and fix the bad decisions.

Lets say you work with a bunch of people that are setting weekly goals. Each year goals are appended in a particular format to a table referenced by everyone to present goals throughout the organization.

Long long ago in a galaxy far far away someone decided set the goal date column as type VARCHAR() so now you have goals associated with strings that range from ‘2025-05’ to ‘2025-02-05’ to ‘02/05/2025’. In fact for a single goal, the weeks may be recorded in wildly different formats each year based on whoever uploads them.

THIS IS A BAD PROCESS.

This should be screaming bad process, as you disentangle different week to week formatting changes each year or worse what if someone updates it in the middle of the year and your logic just suddenly breaks… Your best way to test that logic in the first place would have been to establish a proper schema and not use a VARCHAR to store a date, but we’ve absolutely blown the rails off that one.

CREATE TEMP TABLE TEST123 AS (
SELECT '2024-12-05'::DATE AS SHIP_DATE
);

-- ERROR: invalid input syntax for type date: "2024-49"
-- Yes! This is what we want
INSERT INTO TEST123 VALUES
('2024-49');

Stop putting band-aids on and fix it… that’s the only way this is going to get better. End of story. But there’s a lot of important dependencies on this table we’d have to change a lot of logic… its a big risk There’s a much bigger risk in parsing semi-random strings to match dates then making it right.

Change your automatic response from ~“What do I need to do to make this work”~ to ~“How do I fix this and make this better for everyone moving forward”~. Everyone will benefit and your organization will be more flexible and testable.

Flexible and testable, good qualities to live by.