I have a table that contains date in format YYYYMMDD like 20230120. I need to build string that contains: current date year from block_date month from block_date with - delimeter :

SELECT
CONCAT(TO_CHAR(CURRENT_DATE, 'DD.MM.YY'), '_', EXTRACT (year from to_date(block_date::text, 'yyyymmdd')), '_', EXTRACT (month from to_date(block_date::text, 'yyyymmdd')))
from test

It returns: 20.01.23_2023_1 but I expect 20.01.23_2023_01

I have 2 questions:

1. For month from block_date i expect 2 digits, like 01, 02, 11...
2. Can I simpify my select?

CodePudding user response：

Use to_char() on the converted date as well:

CONCAT(TO_CHAR(CURRENT_DATE, 'DD.MM.YY'), '_', to_char(to_date(block_date::text, 'yyyymmdd'), 'yyyy_mm'))