TechQA.

Linked Questions

Popular Questions

How to convert date to YYYY-MM without to_char

Asked by At

I need to 1. convert date into yyyy-mm format, and 2. calculate duration in month from start_date and end_date, e.g. duration = 1 month, ...etc. start_date & end_date are date string in PostgreSQL. I can calculate duration in days and convert date into yyyy-mm format.

to_char(TO_DATE(end_payperiod_date,'MM/DD/YY'), 'YYYY-MM')

For month duration calculation, I don't find to_date() can convert into yyyy-mm format in PostgreSQL. to_char() can show yyyy-mm, but I can't do calculation.

Is there any way, I can convert date into yyyy-mm to do calculation?

sql postgresql
Original Q&A

1 Answers

0
Mike Sherrill 'Cat Recall' On

Your requirements are unclear. PostgreSQL easily handles dates and date arithmetic. But values like '2019-12' are not dates. I'll throw together a little table for a demonstration of the problems.

create table test (
  start_date char(10) not null,
  end_date  char(10) not null,
  check (start_date <= end_date)
);

insert into test values 
('2019-12-01', '2020-01-01'), 
('2019-12-31', '2020-01-01'),
('2019-12-01', '2020-01-31'),
('2019-01-31', '2019-03-01');

PostgreSQL can subtract one date from another, but you don't have dates. You have strings. So cast the char or varchar columns as dates, and take the difference. If your database has been around for a while, odds are good that there are values in those columns that can't be cast to valid dates.

select 
  cast (start_date as date), 
  cast(end_date as date), 
  cast(end_date as date) - cast(start_date as date) as difference,
  left(start_date, 7) as start_month,
  left(end_date, 7) as end_month
from test;

start_date   end_date    difference  start_month   end_month
--
2019-12-01   2020-01-01  31          2019-12       2020-01
2019-12-31   2020-01-01   1          2019-12       2020-01
2019-12-01   2020-01-31  61          2019-12       2020-01
2019-01-31   2019-03-01  29          2019-01       2019-03

Note that start_month and end_month are the same for start_month = '2019-12' despite the difference ranging from one to 61 days. And how you want to calculate the difference between January and March is still unclear.

Adapting the query in your comments to the table and column names given here did not return the results you seem to expect.

Related Questions