CODEWITHSUNDEEP
sql-serverpostgresqlvalidationdate
Artem Cherkes
Artem Cherkes

Reputation: 79

Check if valid date in PostgreSQL source

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.

Upvotes: 4

Views: 14813

Answers (3)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95731

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.

Upvotes: 5

Phill Pafford
Phill Pafford

Reputation: 85368

Cast to char and then format how you would like

SELECT TO_CHAR(date_time_column, 'YYYY-MM-DD') AS mssql_date_format
FROM table_name 
WHERE -- your condition here

-- results like: 1900-01-01

Read more here

Upvotes: -1

klin
klin

Reputation: 121804

More complex algorithms in SQL are easier to write (and read) with CTE:

with 
    syntax as (
        select str, 
            str ~ '^\d{4}-\d{2}-\d{2}$' as syntax_ok,
            split_part(str, '-', 1) as syy,
            split_part(str, '-', 2) as smm,
            split_part(str, '-', 3) as sdd
        from test_date),
    toint as (
        select *,
            case when syntax_ok then syy::int else 1900 end yy,
            case when syntax_ok then smm::int else 1 end mm,
            case when syntax_ok then sdd::int else 1 end dd
        from source),
    semantics as (
        select *,
            case
            when mm in (1,3,5,7,8,10,12) then dd between 1 and 31
            when mm in (4,6,9,11) then dd between 1 and 30
            when mm = 2 then 
                case when yy/4*4 = yy and (yy/100*100 <> yy or yy/400*400 = yy)
                then dd between 1 and 29
                else dd between 1 and 28 end
            else false end as valid
        from toint),
    result as (
        select str,
            case when syntax_ok and valid then str
            else '1900-01-01' end as date
        from semantics)
select * from result

The first query checks syntax and split str into three parts, the second query casts the parts as integers, and the third query checks semantics. SQLFiddle.

Your comment shows that you do not need quite as thoroughly checks, but I think that this query can be easily customized to your liking.

Upvotes: 0

Related Questions