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

extract(month name from date in postgresql)
extract(month and year from date in postgresql)
postgres date format dd/mm/yyyy
postgres date query
postgres query timestamp by date
how to change date format in postgresql example
postgresql convert date to integer yyyymmdd
postgresql last 30 days

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:


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

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


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'))
<your table>

Fiddle example

PostgreSQL Date Functions (and 7 Ways to Use Them in Business , PostgreSQL date functions (like DATE_TRUNC, EXTRACT, and AGE) make visits each month by channel, add the channel column and a COUNT to the SELECT The EXTRACT date function allows you to isolate subfields such as year or  The PostgreSQL EXTRACT() function retrieves a field such as a year, month, and day from a date/time value. Syntax. The following illustrates the syntax of the EXTRACT() function: EXTRACT(field FROM source) Arguments. The PostgreSQL EXTRACT() function requires two arguments: 1) field. The field argument specifies which field to extract from the

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'
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.

Documentation: 8.3: Date/Time Functions and Operators, Table 9-26 shows the available functions for date/time value processing, with details appearing in For formatting functions, refer to Section 9.8. extract (​field from interval), double precision, Get subfield; see Section 9.9.1, extract(​month For timestamp values, the number of the month within the year (1 - 12) ; for interval  Database Date Format :: YYYY-MM-DD For example :: 2010-11-26 Text-Field Date Format :: Day, Month Date, Year :: Fri, Nov 26, 10 Now I have tried to compare this value but no response Plz suggest. Select * from table_name where user_id=('11') and to_char(effective_date, $$$$$$$$)=trim('Fri, Nov 26, 10');

for obtain the date form the string you could use



select to_date(fiscal,'YY-Mon') 

Documentation: 9.4: Date/Time Types, For some formats, ordering of day, month, and year in date input is ambiguous Set the DateStyle parameter to MDY to select month-day-year interpretation, DMY to specification giving the number of fractional digits in the seconds field. are implicitly added with appropriate sign accounting. ago negates all the fields​. In MySQL if you want to format date in order to extract only day, month or year you have several different options like: * Date format in MySQL Using EXTRACT function * Date format in MySQL Using DATE_FORMAT * Date format in MySQL USING DAY, MONTH, YEAR * MySQL where clause filter by DAY, MONTH, YEAR This functions are not fully. 1, PostgreSQL 9.

PostgreSQL DATE_PART Function: Extract Year, Month, Week, etc , The DATE_PART() function extracts a subfield from a date or time value. SELECT date_part('century',TIMESTAMP '2017-01-01'); date_part ----------- 21 (1 row). To extract the year from the same timestamp, you pass the year to the field  Select (CASE WHEN DatePart(m,[yourdate]) > 3 THEN DatePart(yyyy,[yourdate])+1 ELSE DatePart(yyyy,[yourdate]) END) AS FiscalYear FROM YourTable. This should give you the end year of your fiscal year. If you need something like "2001-2002" you can use some string functions etc to prodcuce that.

4. Date Handling - SQL Hacks [Book], The ISO format used in both examples (the year followed by the month followed Oracle, PostgreSQL, and MySQL adhere to the SQL standard for representing The DATE type does not exist in SQL Server; you should use the DATETIME type If your dates are coming from an untrusted source, you should still check the  In Postgres how to get all in between fiscal year from starting and end date.My fiscal year starting from 1st july. Suppose i have two date,one of them is '2014-05-17' that is starting date and another date '2017-12-17' that is end date. i want to get all in between fiscal year like below format: fiscal_year-----2014-2015 2015-2016

[Solved] How do I get financial year date range, SELECT QM_FIN_YEAR = CASE WHEN Month(GETDATE()) BETWEEN 4 AND 12 THEN CONVERT(VARCHAR(4),YEAR(GETDATE())) + '-' +  You could have calculated the fiscal month as (MONTH (somedate) + 3) % 12 + 1, which, I think, is less cluttered and much more readable than the other variant. You could likewise calculate the year