470,648 Members | 1,680 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,648 developers. It's quick & easy.

Determine Quarter End and Beginning Dates

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.

Jul 23 '05 #1
3 14252

"Matt" <ma***********@manning-napier.com> wrote in message
news:11**********************@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
Jul 23 '05 #2
> 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


Jul 23 '05 #3
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)
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by p175 | last post: by
2 posts views Thread by Samaba via AccessMonster.com | last post: by
8 posts views Thread by Dominique Vandensteen | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.