Check Whether String Is a Date Postgresql

Check whether string is a date Postgresql

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)

input date validation check calling to_date

Must you do this by patching your server code? Especially when that patch will probably break currently working SQL and will need to be updated when you update PostgreSQL? This is also a bad idea because you'll get in the habit of expecting to_date to work the way your custom to_date works and things will go sideways when you work with an unpatched PostgreSQL. You might want to take into account other people that get stuck working with your custom PostgreSQL server too, how are they supposed to know that to_date really isn't to_date but some modified version? OTOH, perhaps you're doing this for job security.

Why not write your own to_date replacement function, called say strict_to_date, that does the conversion using to_date and then does a simple:

string = to_char(to_date_result, 'yyyymmdd')

comparison? If the to_date and to_char round trip doesn't give you your original result back then you can raise your exception. You'd need to decide what strict_to_date(null) would do of course but that's easy to add.

Consider some simple results:

=> select to_date('20130229','yyyymmdd');
  to_date   
------------
 2013-03-01
(1 row)

=> select to_char(to_date('20130229','yyyymmdd'), 'yyyymmdd');
 to_char  
----------
 20130301
(1 row)

Since '20130229' != '20130301' you have your exception. Wrap that in a function and slip in a little comment about why you're doing this and everyone should be happy.

Check if valid date in PostgreSQL source

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'.

It's not clear to me whether the result should be a date or a timestamp. But this shows how you should probably approach the problem in PostgreSQL

with data (ts) as (
  values (timestamp '0214-06-19 00:00:00'), ('1900-01-01 08:00')
)
select 
    ts as source_ts, 
    case when ts < timestamp '1900-01-01' then timestamp '1900-01-01' 
         else ts 
    end as altered_ts
from data;

source_ts             altered_ts
--
0214-06-19 00:00:00   1900-01-01 00:00:00
1900-01-01 08:00:00   1900-01-01 08:00:00

Assuming every date before 1900 should be 1900-01-01 is kind of risky. The value '0214-06-19' is probably a typo for 2014-06-19.

How to check the valid of the PostgreSQL datetime format string taken from user?

That is because it is a valid format.

You should probably define what you consider a valid format and perhaps compose a regular expression for that.

By the way, your code is vulnerable to SQL injection, which is particularly dangerous since you are trying to verify unreliable data here.

Postgres Date Cast from a String

You should be taking a substring of the first nine, not ten, characters:

SELECT TO_DATE(SUBSTRING("date", 1, 9), 'DD-MON-YY') AS date
FROM tbl
ORDER BY date DESC;

How to compare dates in datetime fields in Postgresql?

@Nicolai is correct about casting and why the condition is false for any data. i guess you prefer the first form because you want to avoid date manipulation on the input string, correct? you don't need to be afraid:

SELECT *
FROM table
WHERE update_date >= '2013-05-03'::date
AND update_date < ('2013-05-03'::date + '1 day'::interval);

Recognizing invalid dates in postgresql

http://www.tek-tips.com/viewthread.cfm?qid=1280050&page=9

A more generic approach than the above:

create function safe_cast(text,anyelement) 
returns anyelement 
language plpgsql as $$ 
begin 
    $0 := $1; 
    return $0; 
    exception when others then 
        return $2; 
end; $$;

Used like this:

select safe_cast('Jan 10, 2009', '2011-01-01'::timestamp)
select safe_cast('Jan 10, 2009', null::timestamp)

Credited to the friendly dudes at the #postgresql irc channel. :)

---

Related Topics