Dates are the most reliably mishandled data type in software. They look simple, they carry hidden regional assumptions, and a wrong one usually fails silently rather than loudly. This page collects the formats worth knowing, every strftime code in one table, and the specific traps that turn a working system into a subtly wrong one.
If you only take one thing
Use 2026-08-02T14:30:00Z — ISO 8601 with an explicit UTC offset — for anything stored, logged or transmitted. It sorts correctly as a plain string, cannot be misread, and every language parses it. Reformat for humans only at the moment of display.
ISO 8601: the standard worth knowing properly
ISO 8601 orders components from largest to smallest, which produces its most useful property almost by accident: chronological order and alphabetical order are the same. Sorting ISO date strings as text sorts them by time. No parsing required, which is why log files and filenames use it.
⚠️ A timestamp with no offset means nothing
2026-08-02T14:30:00 is 14:30 somewhere, and the string does not say where. Worse, parsers disagree about what to assume: some treat it as UTC, some as local time. The same string can therefore represent moments hours apart depending on which library reads it. Always include Z or an explicit offset.
ISO 8601 versus RFC 3339
These are frequently spoken of as the same thing. They overlap heavily but are not identical, and the difference matters when writing a parser or an API specification.
| Form | ISO 8601 | RFC 3339 |
|---|---|---|
2026-08-02T14:30:00Z | Valid | Valid |
20260802T143000Z (no separators) | Valid | Invalid |
2026-W31-7 (week date) | Valid | Invalid |
2026-214 (ordinal date) | Valid | Invalid |
2026-08-02 (date only) | Valid | Invalid — time is required |
P3Y6M4D (duration) | Valid | Invalid |
2026-08-02t14:30:00z (lowercase) | Invalid | Valid (discouraged) |
2026-08-02 14:30:00Z (space) | Invalid | Valid by extension |
The practical summary: RFC 3339 is the strict subset you want for APIs, because it removes the exotic forms and guarantees a fully-specified instant. ISO 8601 is the broader standard covering weeks, durations and intervals. If a specification says "ISO 8601", a careful implementer should ask which subset is actually meant.
The complete strftime reference
The strftime format codes originate in C and are used almost unchanged by Python, PHP, Ruby, Perl, and the Unix date command. Learn them once and they work nearly everywhere.
Date codes
| Code | Meaning | Example |
|---|---|---|
%Y | Year, four digits | 2026 |
%y | Year, two digits | 26 |
%C | Century | 20 |
%G | ISO week-numbering year | 2026 |
%m | Month, zero-padded | 08 |
%B | Month, full name | August |
%b or %h | Month, abbreviated | Aug |
%d | Day of month, zero-padded | 02 |
%e | Day of month, space-padded | " 2" |
%j | Day of year | 214 |
%A | Weekday, full name | Sunday |
%a | Weekday, abbreviated | Sun |
%u | Weekday number, Monday = 1 | 7 |
%w | Weekday number, Sunday = 0 | 0 |
%V | ISO week number (01–53) | 31 |
%U | Week of year, Sunday first | 31 |
%W | Week of year, Monday first | 31 |
Time codes
| Code | Meaning | Example |
|---|---|---|
%H | Hour, 24-hour, zero-padded | 14 |
%I | Hour, 12-hour, zero-padded | 02 |
%p | AM or PM | PM |
%M | Minute | 30 |
%S | Second | 00 |
%f | Microseconds (Python) | 123456 |
%z | UTC offset | +0530 |
%Z | Timezone name | IST |
%s | Unix timestamp | 1785681000 |
Composite shortcuts
| Code | Equivalent to | Example output |
|---|---|---|
%F | %Y-%m-%d | 2026-08-02 |
%T | %H:%M:%S | 14:30:00 |
%R | %H:%M | 14:30 |
%D | %m/%d/%y | 08/02/26 |
%c | Locale date and time | Sun Aug 2 14:30:00 2026 |
%x | Locale date | 08/02/2026 |
%X | Locale time | 14:30:00 |
%% | A literal percent sign | % |
🚨 The %G and %Y trap
Using %Y-W%V instead of %G-W%V produces a wrong year at every year boundary. On 1 January 2027, %Y gives 2027 but %V gives week 53 — of 2026. The result, 2027-W53, is a week that does not exist. This bug surfaces once a year, in early January, in reporting code that ran fine for eleven months.
Common format strings
| Result | strftime |
|---|---|
| 2026-08-02 | %Y-%m-%d |
| 2026-08-02 14:30:00 | %Y-%m-%d %H:%M:%S |
| 2026-08-02T14:30:00Z | %Y-%m-%dT%H:%M:%SZ |
| 02/08/2026 | %d/%m/%Y |
| 08/02/2026 | %m/%d/%Y |
| 2 August 2026 | %-d %B %Y |
| August 2, 2026 | %B %-d, %Y |
| Sun, 02 Aug 2026 | %a, %d %b %Y |
| 2:30 PM | %-I:%M %p |
| 20260802_143000 (filenames) | %Y%m%d_%H%M%S |
| 2026-W31 | %G-W%V |
The - flag (as in %-d) strips the leading zero on Linux and macOS. Windows uses %#d instead — a genuine portability difference that catches people moving scripts between platforms.
The ambiguity problem
Consider 03/04/2026. It is a valid date in at least two reading conventions, and they refer to different days a month apart:
| Convention | Used in | Reads as |
|---|---|---|
| MM/DD/YYYY | United States, Philippines | 3 April 2026 |
| DD/MM/YYYY | Most of the world | 4 March 2026 |
| YYYY/MM/DD | China, Japan, Korea, Iran | Not applicable to this string |
What makes this dangerous is not that it fails — it is that it succeeds incorrectly. A spreadsheet import, a CSV parse or a form submission will happily accept the wrong day and produce no error. The mistake surfaces weeks later when something is scheduled on the wrong date.
✅ Formats that cannot be misread
2026-08-02— ISO. Unambiguous everywhere. Use for data.2 Aug 2026— a named month cannot be confused with a day number. Use for display.Aug 2, 2026— same, in American order.
Any format with three numbers separated by slashes is ambiguous. If your interface must show one, label the expected order explicitly next to the field.
Formatting in each language
⚠️ MySQL's format codes are not strftime
MySQL uses %i for minutes and %M for the full month name — the reverse of strftime, where %M is minutes and %B is the month name. Writing '%Y-%m-%d %H:%M' in MySQL produces 2026-08-02 14:August. It parses, it runs, and it is wrong.
Database column types
| Database | Type | Stores timezone? | Notes |
|---|---|---|---|
| PostgreSQL | TIMESTAMPTZ | Yes | Stores UTC, converts on read. Use this. |
| PostgreSQL | TIMESTAMP | No | A wall-clock reading with no context |
| PostgreSQL | DATE | No | Correct for birthdays and anniversaries |
| MySQL | TIMESTAMP | Converts | UTC internally; range ends in 2038 |
| MySQL | DATETIME | No | Wider range, no conversion |
| SQLite | TEXT | If you include it | No date type — store ISO 8601 strings |
| SQL Server | DATETIMEOFFSET | Yes | The one to use |
One nuance worth stating: a date without a time is sometimes correct. A birthday is not an instant — someone born on 2 August was born on 2 August regardless of which timezone you view it from. Storing it as a UTC timestamp introduces a bug where the date shifts by a day for users west of Greenwich. Use a plain DATE for calendar dates and a timestamp for events.
The recurring pitfalls
- Two-digit years. Is
%y= 30 the year 1930 or 2030? Every library has a cutoff and they do not all agree. Use four digits. - Assuming a day has 86,400 seconds. On DST transition days it has 23 or 25 hours. Adding 86,400 to a timestamp does not reliably produce "the same time tomorrow".
- Timezone abbreviations. "CST" means Central Standard Time, China Standard Time or Cuba Standard Time. Use IANA identifiers like
America/Chicago. - Local time at midnight. Storing "2026-08-02 00:00:00" in local time and reading it in UTC can shift it to the previous day. This is the single most common date bug in reporting.
- Sorting formatted dates.
"12/01/2026"sorts before"02/01/2027"as text. Only ISO format sorts correctly as a string. - Leap years. Divisible by 4, except centuries, unless divisible by 400. 2000 was a leap year; 1900 and 2100 are not.
- The 29 February anniversary. What is "one year after 2024-02-29"? Libraries disagree between 28 February and 1 March. Decide explicitly.
Converting a Unix timestamp?
Paste a timestamp and get a readable date in any timezone, or go the other way — instantly, in your browser.
Open the Timestamp Converter →The working rules
- Store and transmit as
2026-08-02T14:30:00Z. Always include the offset. - Store UTC, display local. Convert at the last possible moment.
- Use IANA timezone names, never three-letter abbreviations.
- Never use slash-separated numeric dates for data. They are silently ambiguous.
- Pair
%Gwith%V, never%Y, or January will break. - Use a plain DATE type for birthdays and calendar dates that have no instant attached.
- Never write your own date parser. Every edge case listed here is already handled by your standard library.
Frequently Asked Questions
What is the difference between ISO 8601 and RFC 3339?
RFC 3339 is a stricter profile of ISO 8601 built for internet protocols. ISO 8601 permits forms RFC 3339 forbids — omitting separators (20260802), week dates (2026-W31-7), durations, and ordinal dates. RFC 3339 requires the full YYYY-MM-DDTHH:MM:SSZ shape with an explicit offset. Almost every valid RFC 3339 timestamp is valid ISO 8601, but not the reverse.
What does the T mean in a date like 2026-08-02T14:30:00Z?
T is a literal separator marking where the date ends and the time begins, required so parsers do not have to guess. The trailing Z means Zulu time, military phonetic for the zero meridian — it declares the timestamp is in UTC with a zero offset. 2026-08-02T14:30:00Z and 2026-08-02T14:30:00+00:00 are identical.
What date format should I use in a database or API?
Store UTC and serialise as ISO 8601 with an explicit offset: 2026-08-02T14:30:00Z. It sorts correctly as plain text, has no regional ambiguity, is parseable by every language's standard library, and is human-readable in a log file. Never store or transmit a date without timezone information.
Why is 03/04/2026 ambiguous?
In the United States it reads as March 4th; in most of the rest of the world as 3rd April. There is no way to tell which was meant from the string alone, and both are plausible dates. Because the ambiguity is silent — no software errors, it just produces the wrong day — this format should never be used for data interchange, only ever for display in a known locale.
What is an ISO week date?
A format like 2026-W31-7 identifying the year, ISO week number and weekday. ISO weeks start on Monday, and week 1 is the week containing the year's first Thursday. This means early January can belong to the previous ISO year — 1 January 2027 falls in ISO week 53 of 2026 — which regularly breaks reporting code that assumes the calendar year and ISO year match.