Why it goes wrong so often
Dates look simple and are not. Time zones shift, clocks change twice a year in many places, and a value stored without its zone is ambiguous forever. The resulting bugs are subtle, intermittent, and usually found in financial reports.
Store instants in UTC, display in local
One representation in the database, converted at the edges for display and input. Mixed conventions in one system produce errors that only appear for some users at some times of year, which is the hardest kind to reproduce.
A timestamp without a time zone is a guess that looks like data.
A date is not an instant
Someone’s birthday or an invoice date is a calendar date, not a moment. Storing it as a timestamp means it shifts by a day for some users — a classic source of off-by-one reporting nobody suspects.
Ranges need explicit boundaries
“This month” depends on whose month, ending at which instant. Reports built on vague boundaries disagree with each other, and reconciling them is exactly the kind of discrepancy that surfaces during a migration.
Never build them from strings
Formatting and parsing by hand produces locale bugs where day and month swap silently. Use the platform’s date handling, and store in an unambiguous format.
Test the awkward days
Clock changes, leap days, and month ends belong in your test cases. They arrive on a schedule, which makes them the rare category of bug you can genuinely prevent — and they should be part of what a scheduled job is checked against.