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

Datediff HALF YEAR?? and dateadd

Hi Guys

i have this query

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

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
Asked:
tim_carter
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
half year = 6 months or 2 quarters ...
so, where is the problem?
0
 
openshacCommented:
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

Open in new window

0
 
tim_carterAuthor Commented:
Thanks guys

DateAdd(quarter,DATEDIFF(quarter,t2.StartDate,'10/14/2008'),t2.StartDate) NextInvoiceQuarter,
DateAdd(quarter,2*DATEDIFF(quarter,t2.StartDate,'10/14/2008'),t2.StartDate) NextInvoiceHalfYear,
DateAdd(year,DATEDIFF(year,t2.StartDate,'10/14/2008'),t2.StartDate) NextInvoiceYear,

works perfectly
0
 
tim_carterAuthor Commented:
Guys it doesnt work right

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

SELECT
CASE t2.HowToCharge
      WHEN 1 THEN CONVERT(char,DateAdd(m,DATEDIFF(m,t2.StartDate,@StartTime),t2.StartDate),101)
      WHEN 3 THEN CONVERT(char,DateAdd(q,DATEDIFF(q,t2.StartDate,@StartTime),t2.StartDate),101)
      WHEN 6 THEN CONVERT(char,DateAdd(q,2*DATEDIFF(q,t2.StartDate,@StartTime),t2.StartDate),101)
      WHEN 12 THEN CONVERT(char,DateAdd(year,DATEDIFF(year,t2.StartDate,@StartTime),t2.StartDate),101)
      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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now