Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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")
like image 636
user3015541 Avatar asked Aug 19 '14 02:08

user3015541


People also ask

Is valid date in PostgreSQL?

PostgreSQL has a much greater range for timestamps than SQL Server does for datetimes. In PostgreSQL, '0214-06-19 00:00:00' is a valid timestamp. So is '0214-06-19 00:00:00 BC'. Assuming every date before 1900 should be 1900-01-01 is kind of risky.

Is date a data type in PostgreSQL?

PostgreSQL supports a DATE data type to store date values. It takes 4 bytes of storage and ranges from 4713 BC to 5874897 AD. PostgreSQL uses the yyyy-mm-dd format for storing and inserting date values.

How do I convert text to date in PostgreSQL?

The TO_DATE function in PostgreSQL is used to converting strings into dates. Its syntax is TO_DATE(text, text) and the return type is date. The TO_TIMESTAMP function converts string data into timestamps with timezone. Its syntax is to_timestamp(text, text) .

What is the current date in PostgreSQL?

The PostgreSQL CURRENT_DATE function returns the current date (the system date on the machine running PostgreSQL) as a value in the 'YYYY-MM-DD' format. In this format, 'YYYY' is a 4-digit year, 'MM' is a 2-digit month, and 'DD' is a 2-digit day. The returned value is a date data type.


1 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)
like image 78
ntalbs Avatar answered Sep 27 '22 18:09

ntalbs