Number of trimesters between two dates

Question

Number of trimesters between two dates

Rem70Rem

SSCertifiable

Points: 6166
More actions
August 10, 2010 at 11:42 am

#236476

Hi,
I need to find the number of trimester between 2 dates.
For example if I want to find the number of relative quarter between two dates I use "select datediff(qq, '2010-03-30', '2010-04-01')". This will return 1 since they are not in the same quarter.
For trimester, there is not parameter to use datediff, si I need to build a function to do it. To find a trimester I am using this "select ((MONTH('2010-09-01')-1) / 4) + 1", but I need to find the number of trimester between two dates.
I was thinking to use something like this "select cast (datediff(dd, '2010-08-30', '2010-09-01')/121.66666 as int)" where 121.66666 is the average days in a trimester. This doesn't works since when there is a switch of trimester the number of days is smaller then 121.66666, we need almost 3 months difference to have a valid result.
Any hint?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

I think you need to post a usable definition of exactly what a trimester is before someone can write code to give you a trimester.

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 2

Here my trimesters:

A trimester is period of three months

Jan to Apr = Trimester #1

May to Aug= Trimester #2

Sep to Dec = Trimester #3

I also need to get the number of trimesters between 2 dates even if they are not in the same year. For example, for the number of quarter between 2 dares not in the same year...select datediff(qq, getdate(), '2009-04-01')=-5

Thanks for your help

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Rem-487422 (8/10/2010)
Here my trimesters:
A trimester is period of three months
Jan to Apr = Trimester #1
May to Aug= Trimester #2
Sep to Dec = Trimester #3
Thanks for your help

Your trimesters have four months, not three months.

select

Trimester = ((datepart(month,a.MyDate)-1)/4)+1 ,

a.*

from

( -- Test Data

select MyDate = dateadd(month, 0,getdate())union all

select MyDate = dateadd(month, 1,getdate())union all

select MyDate = dateadd(month, 2,getdate())union all

select MyDate = dateadd(month, 3,getdate())union all

select MyDate = dateadd(month, 4,getdate())union all

select MyDate = dateadd(month, 5,getdate())union all

select MyDate = dateadd(month, 6,getdate())union all

select MyDate = dateadd(month, 7,getdate())union all

select MyDate = dateadd(month, 8,getdate())union all

select MyDate = dateadd(month, 9,getdate())union all

select MyDate = dateadd(month,10,getdate())union all

select MyDate = dateadd(month,11,getdate())union all

select MyDate = dateadd(month,12,getdate())

) a

Results:

Trimester MyDate

----------- -----------------------

2 2010-08-10 14:40:03.560

3 2010-09-10 14:40:03.560

3 2010-10-10 14:40:03.560

3 2010-11-10 14:40:03.560

3 2010-12-10 14:40:03.560

1 2011-01-10 14:40:03.560

1 2011-02-10 14:40:03.560

1 2011-03-10 14:40:03.560

1 2011-04-10 14:40:03.560

2 2011-05-10 14:40:03.560

2 2011-06-10 14:40:03.560

2 2011-07-10 14:40:03.560

2 2011-08-10 14:40:03.560

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 4

Thanks Michael, but I need to find the number of trimesters between 2 dates, even if they are no tin the same year.

Ex:

2010-05-10 and 2010-07-01 will be 0

2010-04-30 and 2010-05-01 will be 1

2010-05-30 and 2009-12-30 will be 2

2010-05-30 and 2008-12-30 will be 6

many thanks for your help.

drew.allen SSC Guru Points: 76978 More actions · Answer 5

I'm comparing the difference in months between the zero date and each date, dividing that by four (using integer division) and then subtracting the two values.

DateDiff(Month, 0, FirstDateField)/4 - DateDiff(Month, 0, SecondDateField)/4

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 6

Thx all.

I did write function but the one of Drew is way more elegant.

Rem

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 7

Are you using 'trimester' in a medical sense? If so, you should be aware that the first trimester is two weeks longer than the next two, since it's calculated off the LMP date.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 8

I am using the trimester for financial purposes not medical. Thanks for bringing this point...