CODEWITHSUNDEEP
sqlstringpostgresqldatetime
Manu
Manu

Reputation: 1120

Concat year and month but with a fixed size

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

Upvotes: 1

Views: 387

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

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:

Upvotes: 2

GMB
GMB

Reputation: 222432

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

Upvotes: 2

Related Questions