CODEWITHSUNDEEP
postgresqldateconcatenation
Chef1075
Chef1075

Reputation: 2724

Producing date from year and month values in PostgreSQL

Hello I'm having two problems with converting a concatenated date value into an actual date.

I've tired looking here to convert the concatenated value with to_char(DATE ...) but I keep getting odd dates. I think it is because my month does not have a zero padding in front of it.

This is my base query:

SELECT 

   expiry_month,
   expiry_year,
   to_date(CONCAT(expiry_year, expiry_month), 'YYYY/MM'),

FROM thisTable

Here is an example of the data output:

expiry_month    expiry_year   concatvalues
9               2018          20189-01-01   
1               2019          20191-01-01
5               2016          20165-01-01
3               2019          20193-01-01
10              2017          201710-01-01
2               2020          20202-01-01

I think I need to LPAD() my month value to get the correct date parsed. E.g. 01 not 1, and 05 not 5.

However when I try to LPAD the month values it does not work. I've tried:

lpad(to_char(expiry_month),2,'0'),

I get this error 'HINT: No function matches the given name and argument types. You might need to add explicit type casts.'

Which I don't understand because lpad is a function. Any suggestion on how to use LPAD()?

Thank you for the advice.

EDIT 1

I've tried to update the to_date() function with this code:

to_date(CONCAT(payment_cards.expiry_year || ' - ' || payment_cards.expiry_month || ' - 01'), 'YYYY-MM-01') and now it is throwing a different error:

ERROR: invalid value "- " for "MM" DETAIL: Value must be an integer.

I'm still thinking I need to pad the month date?

Upvotes: 4

Views: 8777

Answers (2)

MZA
MZA

Reputation: 1057

Use:

make_date(year int, month int, day int)

like:

make_date(expiry_year, expiry_month, 1)

Postgresql documentation

Upvotes: 3

joanolo
joanolo

Reputation: 6328

There's a '/' missing:

SELECT

   expiry_month,
   expiry_year,
   to_date(CONCAT(expiry_year, '/', expiry_month), 'YYYY/MM') AS the_start_of_year_month

FROM thisTable ;

will produce:

expiry_month | expiry_year | the_start_of_year_month
-----------: | ----------: | :----------------------
           9 |        2018 | 2018-09-01             
           1 |        2019 | 2019-01-01             
           5 |        2016 | 2016-05-01             
           3 |        2019 | 2019-03-01             
          10 |        2017 | 2017-10-01             
           2 |        2020 | 2020-02-01

The date format is specifying '/' and it wasn't there, so, the whole text was taken as the year, and the month and day were taken as 1/1. CONCAT('2018','9') was just returning '20189' (which is a valid year).

dbfiddle here

Upvotes: 6

Related Questions