CODEWITHSUNDEEP
sqlpostgresqldate
user3015541
user3015541

Reputation: 357

Check whether string is a date Postgresql

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")

Upvotes: 18

Views: 34285

Answers (3)

ILya Kuzmin
ILya Kuzmin

Reputation: 105

Checked is null date. Without creating a function, you can also. Var example = now()

select coalesce(extract(day from now()),'0') <> 0

Upvotes: 0

Mark Warburton
Mark Warburton

Reputation: 517

@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;

Upvotes: 2

ntalbs
ntalbs

Reputation: 29458

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)

Upvotes: 31

Related Questions