agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19650: to_date()/to_timestamp() silently truncate 4+-digit day-of-year input to the first three digits
Date: Thu, 03 Sep 2026 06:45:25 +0000
Message-ID: <19650-d7ea430084cdc44a@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19650
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   x86_64
Description:        

The documentation states that DDD (day of year) accepts values 001-366 and
IDDD (ISO day of year) 001-371, and out-of-range values raise an error. That
check works for 3-digit input, but for input of 4 or more digits the parser
silently keeps only the first three digits and treats them as a valid day
number:

SELECT to_date('2024 1000', 'YYYY DDDD');
-- 2024-04-09      (parsed as day 100 — the first three digits)

SELECT to_date('2024 1234', 'YYYY DDDD');
-- 2024-05-02      (parsed as day 123)

SELECT to_date('2024 10000', 'YYYY DDDD');
-- 2024-04-09      (5-digit input, still day 100)

SELECT to_date('2024 999', 'IYYY IDDD');
-- 2026-09-25      (IDDD 999 silently accepted; an ISO year has at most 371
days)

```
hunt@(null)=# SELECT to_date('2024 1000', 'YYYY DDDD');
-- 2024-04-09      (parsed as day 100 — the first three digits)

SELECT to_date('2024 1234', 'YYYY DDDD');
-- 2024-05-02      (parsed as day 123)

SELECT to_date('2024 10000', 'YYYY DDDD');
-- 2024-04-09      (5-digit input, still day 100)

SELECT to_date('2024 999', 'IYYY IDDD');
-- 2026-09-25      (IDDD 999 silently accepted; an ISO year has at most 371
days)
  to_date
------------
 2024-04-09
(1 row)

  to_date
------------
 2024-05-02
(1 row)

  to_date
------------
 2024-04-09
(1 row)

  to_date
------------
 2026-09-25
(1 row)

hunt@(null)=# SELECT to_date('2024 367', 'YYYY DDD');
-- ERROR:  date/time field value out of range: "2024 367"
ERROR:  date/time field value out of range: "2024 367"
```

view thread (3+ messages)  latest in thread

Message-ID: <19650-d7ea430084cdc44a@postgresql.org>
Permalink:  ../19650-d7ea430084cdc44a@postgresql.org/
Also on:    postgresql.org/message-id/19650-d7ea430084cdc44a@postgresql.org

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, 303677365@qq.com
  Subject: Re: BUG #19650: to_date()/to_timestamp() silently truncate 4+-digit day-of-year input to the first three digits
  In-Reply-To: <19650-d7ea430084cdc44a@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox