Scripting Advice - Start and End date of Quarter

rayonsusan · 2024-10-08T02:54:05+00:00

There was an error rendering this rich post.

Hi All,

I have a scenario where I need to find the start and end dates of Quarter. How to calculate Quater Start and End Date from Time Dimension using script?

I found a post regarding this, but I couldn't get the answer.

I would really appreciate if someone could help me with this.

Thank You in advance!

Find more posts tagged with

Accepted answers

rayonsusan

Thank You so much guys!! You are all really helpful. It gave me an idea.

I ended up using the below code and it worked for me. I am posting this hoping it would help somebody who needs it.

DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, [Datecolumn]) + 1, 0)) AS 'Quarter End Date'

Explanation: Let’s say we want to find the end date of the quarter for October 8, 2024.

Calculate the number of quarters:

DATEDIFF(QUARTER, 0, '10-08-2024') returns the number of quarters from the base date to October 8, 2024.

Add one quarter:
DATEADD(QUARTER, DATEDIFF(QUARTER, 0, '10-08-2024') + 1, 0) gives us the start date of the next quarter, which is January 1, 2025.

Subtract one day:
DATEADD(DAY, -1, '01-01-2025') gives us December 31, 2024, which is the end date of the quarter for October 8, 2024.

This would result as:
Input Date:
10-08-2024

Quarter End Date: 12-31-2024

All comments

Riina Silverberg

Hi rayonsusan,

Can you clarify couple of things, please.

Are we talking about a standard year ie Q1=Jan to March, Q2=April to June, etc?
Are you using the resulting dates in a script, calculation? Is there a need for the date to be in certain format?

Riina

pijpema

Suggest you verify if the data in Financial Periods can help you. In table tfgld007 you can find the start-dates of the months by year. With that you also know the day before. Usually one of the 3 period types matches a calendar year.

Fabiano Silva

@rayonsusan

Do you tried the function utc.add() ?

If you have a start day, you just put to add 3 months

in_utc = date.to.utc(2024,1,1,0,0,0)    

res = utc.add( in_utc, 0, 3, 0, 0, 0, 0, ou_utc )

I hope help you!