CODEWITHSUNDEEP
sql
Catherine
Catherine

Reputation: 1

How can I create a new column for Fiscal Year based on custom date ranges in SQL?

I'm looking to create a new column that includes fiscal year based on custom ranges of the Date of Distribution field (See my query below) in SQL. This is probably relatively simple but I've played around a bit with no luck.

Values I wish to create and range definitions:

A very simplified version of my current query:

Select 
    TRANSACTION_Table.DATE_OF_DISTRIBUTION
    Datename(Month, TRANSACTION_Table.DATE_OF_DISTRIBUTION) as 'Transaction Month',
    TRANSACTION_Table.AMOUNT
From 
    Transaction_Table
Where 
    Transaction_Table.Date_Of_Distribution between '7/1/2019' And '6/30/2021'

Thank you in advance!

Upvotes: 0

Views: 489

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The simplest way is to add 6 months and extract the year. In Standard SQL, this looks like:

extract(year from Date_Of_Distribution + interval '6 month')

Date/time functions are notoriously database dependent, so the exact syntax depends on the database you are using.

For instance, in SQL Server, it would be:

year(dateadd(month, 6, Date_Of_Distribution))

Upvotes: 1

Related Questions