Check if valid date in PostgreSQL source

Question

I have a remote PG data source where I cannot create custom functions. I have to dump PG data into unicode tables on MSSQL Server 2008 R2. I need an inline PostgreSQL statement that would replace an invalid date with something like '1900-01-01'.

I've done extensive googling to no avail ... thanks.

Answer 1

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.