invalid input syntax for type timestamp

Question

While running the below code i get an error saying invalid input syntax for type timestamp from admission_datetime.

    UPDATE ccsm.stg_demographics_baseline
    SET xx_los_days =
    (CASE WHEN admission_datetime IS NULL OR 
    date_trunc('day',admission_datetime) = ''
    THEN NULL
    WHEN discharge_datetime IS NULL OR 
    date_trunc('day',discharge_datetime) = ''
    THEN date_diff('day', admission_datetime, CURRENT_DATE)
    ELSE
    date_diff('day', admission_datetime, discharge_datetime)
    END);
enter code here

admission_datetime is of type timestamp. eg: 2016-06-22 19:10:25-07 — Sachin, May 30 '18 at 11:52

score 3 · Accepted Answer · answered May 30 '18 at 12:34

See date_trunc documentation:

The return value is of type timestamp or interval with all fields that are less significant than the selected one set to zero (or one, for day and month).

So you can not compare it with an empty string:

date_trunc('day', admission_datetime) = ''

The invalid input syntax for type timestamp error message concerns the empty string (''), not the admission_datetime column.

Furthermore, there is no date_diff function in PostgreSQL. Just subtract one timestamp from another and you will get an interval result:

SELECT timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'

You'll get

interval '1 day 15:00:00'

If you need the difference in days, try this:

SELECT DATE_PART('day', timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00')

The result is 1.

See here for examples of DATEDIFF-like expressions in PostgreSQL.

Thank you for the reply. You are right i just changed that to is null and it worked — Sachin, May 30 '18 at 12:56

invalid input syntax for type timestamp

1 Answers1

Linked