Month calculation is one month off in SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Month calculation is one month off in SP

Author

Topic

14437
Starting Member

7 Posts

Posted - 2005-12-01 : 10:56:51

I am having a problem with calculating the month in a stored procedure. The SP is part of a job which runs on the first day of each month for the previous month's data. When it runs, it is supposed to get the previous month from DateAdd(m,-1,GetDate()). However, what happens is that it gets the month as if it was running DateAdd(m,-2,GetDate()) so instead of getting November this morning, it got October.

Strangely enough, when this problem occurs during the scheduled job run at 0645, then I re-run it manually (by clicking on each step in the DTS package as needed), it runs fine without any trouble. Thus, I cannot reproduce the error.

The code is below. PLEASE make your suggestions! This has been occurring for months now.

CREATE procedure [SPNAME]
@rundate datetime=null
as

DECLARE @m int, @y int
select @m = 0, @y = 0

If(@rundate is null)
begin
select @m = DatePart(mm,DateAdd(mm,-1,GetDate()))
select @y = DatePart(yyyy,DateAdd(mm,-1,GetDate()))
end
Else
begin
select @m = DatePart(mm,@rundate)
select @y = DatePart(yyyy,@rundate)
end

select
Country,
Cast(@m as varchar)+'/'+Cast(@y as varchar) as [Time Period],
--Q1: 3 answers
sum(Cast(q1a1 as TinyInt)) as [Q1:Agree],
sum(Cast(q1a2 as TinyInt)) as [Q1:Neither],
sum(Cast(q1a3 as TinyInt)) as [Q1:Disagree],

--Q2: 2 answers
sum(Cast(q2a1 as TinyInt)) as [Q2:Yes],
sum(Cast(q2a2 as TinyInt)) as [Q2:No],

--Q3: 4 answers
sum(Cast(q3a1 as TinyInt)) as [Q3:Help Desk],
sum(Cast(q3a2 as TinyInt)) as [Q3:Co-worker],
sum(Cast(q3a3 as TinyInt)) as [Q3:Technician],
sum(Cast(q3a4 as TinyInt)) as [Q3:Self],
sum(Cast(q3a5 as TinyInt)) as [Q3:N/A],

--Q4: 6 answers
sum(Cast(q4a1 as TinyInt)) as [Q4:Immediately],
sum(Cast(q4a2 as TinyInt)) as [Q4:Less than 2 hrs],
sum(Cast(q4a3 as TinyInt)) as [Q4:2 to 4 hrs],
sum(Cast(q4a4 as TinyInt)) as [Q4:4 to 8 hrs],
sum(Cast(q4a5 as TinyInt)) as [Q4:2 days],
sum(Cast(q4a6 as TinyInt)) as [Q4:More than 2 days],
sum(Cast(q4a7 as TinyInt)) as [Q4:N/A],

--Q5: 3 answers
sum(Cast(q5a1 as TinyInt)) as [Q5:Satisfied],
sum(Cast(q5a2 as TinyInt)) as [Q5:Neither],
sum(Cast(q5a3 as TinyInt)) as [Q5:Dissatisfied],

--Q6: 4 answers
sum(Cast(q6a1 as TinyInt)) as [Q6:Excellent],
sum(Cast(q6a2 as TinyInt)) as [Q6:Good],
sum(Cast(q6a3 as TinyInt)) as [Q6:Fair],
sum(Cast(q6a4 as TinyInt)) as [Q6:Needs Improvement]

from [table] ss
where DatePart(mm,[DateField])=@m and DatePart(yyyy,[DateField])=@y
group by Country
order by Country

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 11:02:27

Is it being passed a @rundate parameter (i.e. which is wrong!), rather than calculating it for itself?

Kristen

14437
Starting Member

7 Posts

Posted - 2005-12-01 : 11:11:50

Kristen,

No rundate is being passed. I created the SP this way in case I would ever have need of passing a rundate, such as for a different date range.

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 12:42:45

Fair enough. In that case I would be planning to have the SProc "store" all its working variables in some sort of logging table so I could see precisely what parameters it actually ran with.

Kristen

14437
Starting Member

7 Posts

Posted - 2005-12-01 : 12:43:57

Sheesh...never occurred to me. Thanks Kristen. Will have a go at your idea.

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 12:50:53

If you PRINT them they'll wind up in the STEPS of the Job History, won't they? That would be pretty simple ... (if it works like that!)

Kristen

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-12-01 : 14:16:30

Also, I would prefer to construct the WHERE clause this way:

DECLARE @start_date datetime, @end_date datetime

SELECT @start_date = DATEADD(mm, DATEDIFF(mm, 0, getdate()) -2, 0)
SELECT @end_date = DATEADD(mm, DATEDIFF(mm, 0, getdate()) -1, 0)

...

from [table] ss
where [DateField] >= @start_date
and [DateField] < @end_date

(Note that [DateField] is greater than or equal to @start_date, but only less than @end_date, keeping the dates segregated to the previous month).
This should give you better performance because you are not performing a function on the [DateField] column. If this still gives incorrect results, I would make sure that you are actually running this SP on the first day of the month.

14437
Starting Member

7 Posts

Posted - 2005-12-07 : 08:53:16

Thanks for the new suggestions on using PRINT and avoiding the fx call on the datefield.

I had a logged test copy of the job running but no notable issues showed in the table I logged the details to. I will employ these ideas and post findings near the first of the year when the job runs again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources