-3

I'm having trouble with this. If I find an answer I'll include it in my post. SQL to calculate number of days in current quarter, for instance. I can't figure it with Crystal Syntax which is where I fall short.

for starters,

SQL to get 1st day of current quarter

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

SQL to get last day of current quarter

SELECT DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0))

update:

beginning of current quarter in Crystal syntax:

dateserial(year(currentdate),datepart('q',currentdate)*3-2,1)

end of current quarter

dateserial(year(currentdate),datepart('q',currentdate)*3+1,1-1)
jawz101
  • 89
  • 1
  • 11
  • I don't need to account for holidays. I've just tried things in SQL but translating to Crystal is confusing. – jawz101 May 15 '15 at 17:45
  • @jawz101 Please post the formula you have so far and specific issues you're having with it. – Ryan May 15 '15 at 17:47
  • added SQL example to original post. I just can't figure out Crystal Syntax to do something similar. Even having parts of it such as how to return beginning day of current quarter would help. like `SELECT DATEADD("q", DATEDIFF("q", 0, getdate()), 0)` in SQL returns 1st day of current quarter but Crystal Syntax is very confusing to me. – jawz101 May 15 '15 at 17:51
  • sorry... keep changing original post. I'm done making changes to it but left it with an example of trying to get the 1st day of current quarter – jawz101 May 15 '15 at 18:10

1 Answers1

0

Answer in all it's glory :)

DateDiff ("d", dateserial(year(currentdate),datepart('q',currentdate)*3-2,1),currentdate) - 
 DateDiff ("ww", dateserial(year(currentdate),datepart('q',currentdate)*3-2,1), currentdate, crSaturday) -
 DateDiff ("ww", dateserial(year(currentdate),datepart('q',currentdate)*3-2,1), currentdate,crSunday)

shorthand of the final formula:

Datediff("d", startdate, enddate) - 
Datediff("ww", startdate, enddate, crSaturday)-
Datediff("ww", startdate, enddate, crSunday);
jawz101
  • 89
  • 1
  • 11