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

Datediff HALF YEAR?? and dateadd

Guys it doesnt work right

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

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)

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?

And another thing, how can i handle people going back in time. I mean if the StartTime is smaller than StartDate
  • 3
  • 2
2 Solutions
tim_carterAuthor Commented:
Well i can se what im doing wrong.. You cant do it this way because 2*Datediff. will turn into years eventually. How can i get a datediff halfyear? anyone?
Daniel WilsonCommented:
There's something missing from the problem definition here ...

You're not just trying to find half a year from the StartTime or half a year from the StartDate ... you're trying to find ... what?

If the start point is clear then adding either 2 quarters or 6 months should get you what you want.
tim_carterAuthor Commented:
Everymonth i will change the @startdate to the current date. (month). and the field NextInvoiceDate should tell me the date for each client when they should be invoiced. Now the Quarterly, and Yearly and ofcourse monthly works fine. But i cant seem to get the Helf Year working. Do you have an example of how to do that? Because the way i have done it in the example above, will never work.

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

The only time that 1/2 year (rounded since I assume you're not looking for down to the minute/second differences) isn't going to be 182 days is during a leap year in which it will be 183.  

So for the 1/2 year (dealing with leap year):

WHEN 6 THEN CONVERT(char,DateAdd(q,2*DATEDIFF(q,t2.StartDate,@StartTime),t2.StartDate),101)

convert(char(10), dateadd(d, (datediff(d,getdate(), dateadd(yy,1,getdate())))/2 ,t.2startdate),101)

But this all seems like overkill to get more precise than +6 months.
Daniel WilsonCommented:
Tim, can you lay out several start dates & start times along with what you get & what you want to get?  I think that's the piece were missing.

If I understand your original post correctly, it looks like this:

StartDate    @StartTime    CurrentAnswer    CorrectAnswer
10/01/2008  10/01/2008   10/01/2008            10/01/2008
10/01/2008  11/01/2008   10/01/2009             04/01/2009
tim_carterAuthor Commented:
dont think it can be done, but thanks for the help guys
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

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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