• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1251

# Datediff HALF YEAR?? and dateadd

Hi Guys

i have this query

this gives me the nextquarter invoice date.
Now my problem is that sum customers needs to be charged every half year. How do i calculate that? it doesnt seem like datediff has a half year function?
0
tim_carter
• 2
1 Solution

Billing EngineerCommented:
half year = 6 months or 2 quarters ...
so, where is the problem?
0

Commented:
Try the following:

Of course you may need to pull the value 2 or 6 from another column in your query.

You could have a column called CustomerChargedEveryNQuarters which has values 1 or 2

DateAdd(q, 2 * DATEDIFF(q, t2.StartDate,getdate()),t2.StartDate) NextInvoiceQuarter

or

DateAdd(m, 6 * DATEDIFF(m, t2.StartDate,getdate()), t2.StartDate) NextInvoiceQuarter

or even

DateAdd(q, CustomerChargedEveryNQuarters  * DATEDIFF(q, t2.StartDate,getdate()), t2.StartDate) NextInvoiceQuarter
0

Author Commented:
Thanks guys

works perfectly
0

Author Commented:
Guys it doesnt work right

declare @StartTime datetime
set @StartTime = '10/01/2008'

SELECT
CASE t2.HowToCharge
END
NextInvoiceDate,

Am i missing something here. The half year doesnt work.

If i set @StartTime = 10/01/2008 And the date in the StartDate is 10/01/2008. NextInvoiceDate, will be 10/01/2008 as it should. But as this should be every half year, the next date should be 04/01/2009 RIGHT? but it isnt, if you change StartTime to 04/01/2009 NextInvoiceDate will be 10/01/2009. But if you change StartTime to 03/01/2009 NextInvoiceDate will be 04/01/2009. What am i doing wrong?

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.