1

I have the following dataset:

CREATE TABLE my_table (
    the_debt_id varchar(6) NOT NULL, 
    the_debt_due date NOT NULL, 
    the_debt_paid timestamp NOT NULL
);

INSERT INTO my_table
VALUES ('LMUS01', '2020-08-02', '2020-05-18 11:07:01'), 
       ('LMUS01', '2020-07-02', '2020-05-18 11:07:01'), 
       ('LMUS01', '2020-06-02', '2020-05-18 11:07:01'), 
       ('LMUS01', '2020-05-02', '2020-04-28 02:28:41'),
       ('LMUS01', '2020-04-02', '2020-04-01 06:29:53'),
       ('LMUS01', '2020-03-02', '2020-03-02 07:30:59'),   
       ('LMUS01', '2020-02-02', '2020-01-31 06:58:18');

I just wanted to concat the year and month of the_debt_due but when I try this:

SELECT the_debt_id, 
cast(concat(extract('year' from the_debt_due),extract('month' from the_debt_due)) as integer) 
FROM my_table

It returns five digits for months from January to September (like 20205 for May), and six digits for the rest of the year (like 202012 for December). Is it possible to get six digits? This is the expected output:

the_debt_id    concat     
LMUS01         202008
LMUS01         202007
LMUS01         202006
LMUS01         202005
LMUS01         202004
LMUS01         202003
LMUS01         202002
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Manu
  • 1,070
  • 10
  • 27
  • 1
    Do you need a *string* (for display)? Or a *number* (to do computations)? `extract()`, like you have it, returns type `double precision`, `concat()` force-converts it to `text` and, finally, you cast to `integer` ... – Erwin Brandstetter Sep 04 '20 at 21:30
  • Hello @ErwinBrandstetter, I need an integer for math computations. I see that you have already answered, thank you! – Manu Sep 04 '20 at 22:01

2 Answers2

2

No need for padding logic. Just use to_char():

select the_debt_id, to_char(the_debt_due, 'yyyymm') the_debt_due_char
from mytable
GMB
  • 216,147
  • 25
  • 84
  • 135
2

For display, use to_char() like GMB provided.

If you need an integer, like your cast suggests, this is faster than casting the result of to_char():

SELECT (EXTRACT(year FROM the_debt_due) * 100 + EXTRACT(month FROM the_debt_due))::int;

I tested both when optimizing for this related answer:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228