Is there any function in PostgreSQL that returns Boolean whether a given string is a date or not just like ISDATE() in MSSQL?
ISDATE("January 1, 2014")
-
2Doesn't look like there's a built in one. But you can create your ownConrad Frix– Conrad Frix2014-08-19 02:18:15 +00:00Commented Aug 19, 2014 at 2:18
Add a comment
|
3 Answers
You can create a function:
create or replace function is_date(s varchar) returns boolean as $$
begin
perform s::date;
return true;
exception when others then
return false;
end;
$$ language plpgsql;
Then, you can use it like this:
postgres=# select is_date('January 1, 2014');
is_date
---------
t
(1 row)
postgres=# select is_date('20140101');
is_date
---------
t
(1 row)
postgres=# select is_date('20140199');
is_date
---------
f
(1 row)
3 Comments
Craig Ringer
Note that each
begin ... exception block creates a subtransaction. So this can have a non-trivial performance impact. PostgreSQL really needs a generic way to invoke a datatype cast in a way that either (a) returns null instead of an exception on failure; or (b) invokes it as a test returning a boolean success/failure value.
MarHoff
See also stackoverflow.com/a/6730401/5248144 for a more generic approach
terse
I still needed this answer in 2020. "PostgreSQL really needs a generic way to invoke a datatype cast in a way that either (a) returns null instead of an exception on failure; or (b) invokes it as a test returning a boolean success/failure value." -Craig R. Holy heck, yes please.
@ntalbs answer is good except in the case of NULL values. I don't want is_date to return true if I pass it a NULL value. This tweak gets around that issue:
create or replace function is_date(s varchar) returns boolean as $$
begin
if s is null then
return false;
end if;
perform s::date;
return true;
exception when others then
return false;
end;
$$ language plpgsql;
Comments
Checked is null date. Without creating a function, you can also. Var example = now()
select coalesce(extract(day from now()),'0') <> 0
1 Comment
DatabaseShouter
I just tried this with select coalesce(extract(day from '2025-05-05'),'0') <> 0 and I got the error "ERROR: function pg_catalog.extract(unknown, unknown) is not unique".