I have a query that I would like to schedule in DTS. The criteria of
this query checks for records in the table that are within the current
quarter. Here is what I have.
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?
Thank You."Matt" <matt_marshall@.manning-napier.com> wrote in message
news:1112196440.142834.300110@.z14g2000cwz.googlegr oups.com...
> Hello,
> I have a query that I would like to schedule in DTS. The criteria of
> this query checks for records in the table that are within the current
> quarter. Here is what I have.
> 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?
>
> Thank You.
Quick and dirty solution - see DATEPART in Books Online.
The longer answer is that using DATEPART might not be good for performance
(applying a function to a column prevents MSSQL using an index on that
column), so you may need another approach. One would be to write a stored
proc to return the first and last days of the current quarter, so you can
put them in variables and use them in your query; another would be to create
a calendar table (which is very useful anyway) and join on it in your query.
A couple of other small points - BETWEEN with datetime columns can give you
unexpected results if you don't allow for the time portion. In your case,
this is probably safer:
where submit_date >= '20050101' and submit_date < '20050401'
Also, try to use the YYYYMMDD date format if possible - it will always be
interpreted correctly by MSSQL regardless of client or server settings. More
information here:
http://www.karaszi.com/sqlserver/info_datetime.asp
Simon|||> 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(GetDate()) as varchar(4)) + Right('0' +
CAST((Month(GetDate())-1) / 3 * 3 + 1 as varchar(2)),2) + '01' AND
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST((Month(GetDate())-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(GetDate()) - 1 as varchar(4)) + '0901'
ELSE
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST(Month(GetDate()-1) / 3 * 3 - 2 as varchar(2)),2) + '01'
END
AND
CASE WHEN Month(GetDate()) < 4 THEN
CAST(Year(GetDate()) as varchar(4)) + '0101'
ELSE
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST(Month(GetDate()) / 3 * 3 + 1 as varchar(2)), 2) + '01'
END|||On 30 Mar 2005 07:27:20 -0800, Matt wrote:
>Hello,
>I have a query that I would like to schedule in DTS. The criteria of
>this query checks for records in the table that are within the current
>quarter. Here is what I have.
>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?
>
>Thank You.
Hi Matt,
In addition to the answers Simon and James gave, here's a quick formula
to calculate the first and last date of the quarter:
declare @.test datetime
set @.test = '20051201'
SELECT DATEADD(quarter, DATEDIFF(quarter, '20000101', @.test),
'20000101'),
DATEADD(quarter, DATEDIFF(quarter, '20000101', @.test) + 1,
'19991231')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql
