CODEWITHSUNDEEP
sqlpostgresqlbusiness-intelligence
Luke
Luke

Reputation: 1258

In PostgreSQL, select fiscal year month format as a date column

I have a Postgres database that has a column for the month an event occurred in. This is formatted as a TEXT column type and has the fiscal year preceding the month name as shown below:

fiscal
-------
20-Jul
20-Aug
20-Jan
20-Apr
20-Feb

Say the fiscal in this case starts in Apr 2019 (and refers to the 2019-2020 fiscal year), how would a select query show this as a DATE column type? (assuming just using the first day of the month as the day)

So like below

fiscal    real_date
--------------------
20-Jul    2019-Jul-1
20-Aug    2019-Aug-1
20-Jan    2020-Jan-1
20-Apr    2019-Apr-1
20-Feb    2020-Feb-1

Upvotes: 1

Views: 4709

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think this implements the logic you want:

select (case when extract(month from to_date(substr(fm, 4, 3), 'MON')) >= 4
             then to_date('20' || fm, 'YYYY-MON')
             else to_date('20' || fm, 'YYYY-MON') + interval '1 year'
        end)
from (values ('20-JUL'), ('19-FEB')) v(fm);

Postgres happens to allow you to convert a month to a date. You get a value way far back in time. But I don't think there are any issues because the purpose is only to convert the string to a number.

Upvotes: 2

Andrew
Andrew

Reputation: 8703

This looks horrible, but it seems to work so far...

select

case when extract('month' from (to_date(<your column>,'yy-mon'))) > 4
  then (to_date(<your column>,'yy-mon')) + interval '-1' year
else (to_date(<your column>,'yy-mon'))
              end
from
<your table>

Fiddle example

Upvotes: 2

Related Questions