sqlpostgresqldatepart

Understanding explicit type casts in postgresql


WHEN 'H' = 'E' THEN CAST(DATE_PART('Year', now()) AS CHAR(4))+ '0101'

I want to get the current year and adding to this year the month and the day. When I run the query, I run into below error. Did anyone experience this before?

SQL Error [42883]: ERROR: operator does not exist: character + unknown Hint: No operator matches the given name and argument types. You might need to add explicit type casts.


Solution

  • That method seems really arcane to me. Two alternatives:

    WHEN 'H' = 'E' THEN TO_CHAR(NOW(), 'YYYY') || '0101'
    
    WHEN 'H' = 'E' THEN TO_CHAR(DATE_TRUNC('YEAR', NOW()), 'YYYYMMDD')