CODEWITHSUNDEEP
postgresqldate
sarveshseri
sarveshseri

Reputation: 13985

Postgres - to_date('2016/99/99', 'YYYY/MM/DD') = '2024-06-23' ? How else do we verify valid dates?

The unexpected thing about postgres is that a query like following is considered valid.

SELECT to_date('2016/99/99', 'YYYY/MM/DD') AS date

And you will get date - 2024-06-23 from postgres even when it was clearly an invalid date.

The important requirement here is the ability to use different data formats in different queries.

Note :: Something like '2016/99/99'::timestamp or '2016/99/99'::date will not be able to support varying date formats. Just think the need of a function which takes the date string and a format string and creates a query. ( pls ignore things like sql escaping etc for now)

function(dateString, formatString) {
  return "SELECT * FROM some_table WHERE created_at = to_date('" + dateString + ", '" + formatString + ')";
}

Now, how are we supposed to ensure the date (for dates in custom formats) validity in queries ?

Upvotes: 1

Views: 1705

Answers (2)

user330315
user330315

Reputation:

You could format the result back with the input format and compare both:

with to_check (input, format) as (
  values 
    ('2016/99/99', 'YYYY/MM/DD'), 
    ('88-88-2019', 'dd-mm-yyyy'),
    ('2016-01-01', 'yyyy-mm-dd')
)
select input, to_char(to_date(input, format), format) = input as is_valid
from to_check;

returns:

input      | is_valid
-----------+---------
2016/99/99 | false   
88-88-2019 | false   
2016-01-01 | true

I just saw Eugene's last comment to his answer. That caveat of course applies for this solution as well. 2016/9/9 will fail the above test.

Upvotes: 3

Eugene Lisitsky
Eugene Lisitsky

Reputation: 12875

Try to use another casting method:

psql (9.5.4)

el=# SELECT to_date('2016/99/99', 'YYYY/MM/DD') AS date;
    date
------------
2024-06-23
(1 row)

el=# SELECT '2016/99/99'::timestamp;
ERROR:  value of  date/time out of range: "2016/99/99"
ROW 1: SELECT '2016/99/99'::timestamp;
             ^
HINT:  Perhaps, you need to change setting "datestyle".
el=# SELECT '2016/9/9'::timestamp;
   timestamp
---------------------
2016-09-09 00:00:00
(1 row)

Upvotes: 1

Related Questions