> WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'
I would like to dynamically generate the Quarter End and Quarter
Beginning dates within my where clause based on the date that DTWS
package is being executed on. Can anyone show me how this can be
accomplished?
The easy way to do this is to set up a dates table that has columns for
quarter and year and then join
e.g. If you have a table: dates
D as datetime, Year as integer, Quarter as integer
20050101, 2005, 1
20050102, 2005, 1
....
20051231, 2005, 4
And then in your query:
Join (select Quarter, Year from dates where d = CONVERT(getdate (), datetime,
112) as t
Join dates on submitdate = d
where d.Quarter = t.Quarter and d.year = t.Year
The Hard Way is to calculate it in line:
If you want current quarter then:
WHERE submit_date BETWEEN
CAST(Year(GetDa te()) as varchar(4)) + Right('0' +
CAST((Month(Get Date())-1) / 3 * 3 + 1 as varchar(2)),2) + '01' AND
CAST(Year(GetDa te()) as varchar(4)) + Right('0' +
CAST((Month(Get Date())-1) / 3 * 3 + 4 as varchar(2)), 2) + '01'
Last quarter is harder:
WHERE submit_Date BETWEEN
CASE WHEN Month(GetDate() ) < 4 THEN
CAST(Year(GetDa te()) - 1 as varchar(4)) + '0901'
ELSE
CAST(Year(GetDa te()) as varchar(4)) + Right('0' +
CAST(Month(GetD ate()-1) / 3 * 3 - 2 as varchar(2)),2) + '01'
END
AND
CASE WHEN Month(GetDate() ) < 4 THEN
CAST(Year(GetDa te()) as varchar(4)) + '0101'
ELSE
CAST(Year(GetDa te()) as varchar(4)) + Right('0' +
CAST(Month(GetD ate()) / 3 * 3 + 1 as varchar(2)), 2) + '01'
END