CODEWITHSUNDEEP
sqlpostgresqldatecastingpostgresql-9.2
harnithu
harnithu

Reputation: 93

check values exact format or not in postgres

How to check date values is expected format or not I need to check date values is yyyy-mm-dd format or not if not then display status 0 dob and doj have datatype varchar

Table: Emp

Table: Emp

eid | ename | dob        | doj
1   | abc   | 1900-01-19 | 2019-02-20
2   | dx    | 1900-29-02 | 2019-04-22
3   | xy    | 1989-10-26 | 2018-27-02
4   |ji     | 2000-23-01 | 2019-29-04

based on above data i want check date format should be yyyy-mm-dd if not flag will show like below:

eid | dobdateformatstatus | dojdateformatstatus
1   | 1                   | 1
2   | 0                   | 1
3   | 1                   | 0
4   | 0                   | 0

can you please tell me how to write query to achieve this task in PostgreSQL.

Upvotes: 1

Views: 723

Answers (1)

GMB
GMB

Reputation: 222622

Validating a date is not an easy task, that you don't want to perform manually (you need to take in account the variable number of days per month, not to mention leap years).

Unfortunately Postgres does not have a try_cast() function (as in SQL Server or BigQuery for example): a failed cast is an error.

One method would be to create a user function:

create function try_cast_to_date(p_in text)
returns date 
as $$
begin
    begin
        return $1::date;
    exception 
        when others then return null;
    end;
end;
$$
language plpgsql;

You can then use it in your query:

select 
    eid, 
    ename, 
    (try_cast_to_date(dob) is not null) dobdateformatstatus, 
    (try_cast_to_date(doj) is not null) dojdateformatstatus  
from mytable

Demo on DB Fiddle:

eid | ename | dobdateformatstatus | dojdateformatstatus
--: | :---- | :------------------ | :------------------
  1 | abc   | t                   | t                  
  2 | dx    | f                   | t                  
  3 | xy    | t                   | f                  
  4 | ji    | f                   | f

Upvotes: 1

Related Questions