Question 185056 in PostgreSQL Question posted in
The official documentation can be found here.

POSTGRESQL Error: date format not recognized

SELECT
  v.accounting_date,
  v.payment_date
 FROM 
  (SELECT doc.document_id, 
  max(CASE WHEN doc.name='accountingDate' THEN doc.value END) AS accounting_date,
  max(CASE WHEN doc.name='paymentDate' THEN doc.value END) AS payment_date
  FROM go_appr_doc_variables doc
  GROUP BY doc.document_id) v
 WHERE TO_CHAR(TO_DATE(v.payment_date, 'YYYYMMDD')+8,'yyyymmdd') >= TO_CHAR(NOW(),'yyyymmdd')

v.accounting_date and payment_date values format is ‘yyyymmdd’

I want to do payment_date for +8 days and compare it with the current date.

But there was a problem.

Tags:

2

Answers


  1. 0

    There are multiple ways to manipulate and to compare dates,

    Here’s one option:

    SELECT
  v.accounting_date,
  v.payment_date
 FROM 
  (SELECT doc.document_id, 
  MAX(TO_DATE(CASE WHEN doc.name='accountingDate' THEN doc.value END, 'yyyymmdd')) AS accounting_date,
  MAX(TO_DATE(CASE WHEN doc.name='paymentDate' THEN doc.value END, 'yyyymmdd')) AS payment_date
  FROM go_appr_doc_variables doc
  GROUP BY doc.document_id) v
 WHERE v.payment_date + INTERVAL '8 DAY' >= NOW()::DATE

    explanation for this example:

    • added "TO_DATE()" to the source query, to convert string to
      date field
    • added "+ INTERVAL ‘8 DAY’" – which adds 8 days to a date field
    • added "::DATE" – to truncate the time, keeping only the date part
    Login or Signup to reply.
  2. 0

    I assume doc.value is a properly formatted string. You can compare dates directly after casting NOW() to date eliminating hours etc.

    SELECT
  v.accounting_date,
  v.payment_date
 FROM 
  (SELECT doc.document_id, 
  max(CASE WHEN doc.name='accountingDate' THEN doc.value END) AS accounting_date,
  max(CASE WHEN doc.name='paymentDate' THEN doc.value END) AS payment_date
  FROM go_appr_doc_variables doc
  GROUP BY doc.document_id) v
 WHERE TO_DATE(v.payment_date, 'YYYYMMDD')+8 >= NOW()::date
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search