April 14, 2010 at 2:51 pm
I'm writing a sproc that pulls data for one, or more years (i.e. 2009, 2010). My challenge (I think) is displaying the parms on my result set. the code below is:
Alter Procedure [dbo].[rs_EZine_SignUp]
-- exec [dbo].[rs_EZine_SignUp] '2009', '2010'
(@FromYeardatetime,
@ToYeardatetime
)
As
Set@FromYear= Datepart(Year,@FromYear)
Set@ToYear= Datepart(Year,@ToYear)
SelectAddDate = Convert(Varchar(8),EZ_AddDate,112),
AddYear = Max(Year(EZ_AddDate)),
AddMonth = Max(Month(EZ_AddDate)),
AddDay = Max(Day(EZ_AddDate)),
NewEmails = Count(Distinct(EZ_EMAL)),
[Year] = Datepart(Year,EZ_AddDate),
FromYear = @FromYear,
ToYear = @ToYear
FromSERVER.DATABASE.DBO.TABLE
WhereEZ_EMAL like '_%@_%._%' and
--year(EZ_AddDate) > '2009'
Year(EZ_AddDate) Between @FromYear and @ToYear
GroupBy Convert(Varchar(8),EZ_AddDate,112), Datepart(Year,EZ_AddDate)
OrderBy Convert(Varchar(8),EZ_AddDate,112)
When I run the sproc the "FromYear" and "ToYear" are displayed as below:
FromYear ToYear
1905-07-30 00:00:00.000 1905-07-30 00:00:00.000
The right display should be FromYear 2009 and ToYear 2010
Why are my parameter fields displaying wrong?
John
April 14, 2010 at 3:01 pm
First, you aren't going to get the query to use an index on EZ_AddDate if one exists since you are using the YEAR function on the column.
Is the start and end of the years a fixed date and you just provide the year as input?
April 14, 2010 at 3:08 pm
Hopefully the following sample code will help you.
declare @StartYear int,
@EndYear int;
set @StartYear = 2009;
set @EndYear = 2010;
select @StartYear, dateadd(yy, @StartYear - 1900, 0), @EndYear, dateadd(yy, @EndYear - 1900, 0)
select
*
from
dbo.MyTable
where
EZ_AddDate >= dateadd(yy, @StartYear - 1900, 0) and
EZ_AddDate < dateadd(yy, @EndYear - 1900, 0)
April 14, 2010 at 4:25 pm
When I run the sproc the "FromYear" and "ToYear" are displayed as below:
FromYear ToYear
1905-07-30 00:00:00.000 1905-07-30 00:00:00.000
The right display should be FromYear 2009 and ToYear 2010
Why are my parameter fields displaying wrong?
Look at the following:
declare @FromYear datetime
set @FromYear = '2009'
print DatePart(Year,@FromYear)
set @FromYear = Datepart(Year,@FromYear)
print @FromYear
The initial SET @FromYear='2009' actually worked -- which surprised me. You get '01-01-2009 00:00:00', or just '2009' with the DATEPART(Year,..) function.
However after setting @FromYear= DatePart(Year,@FromYear), @FromYear contains a value in 1905, probably some 2009 DAYS since 1-1-1900 (???).
It appears to be not a good idea to set a DateTime field to the output of a DatePart field.
Rob Schripsema
Propack, Inc.
April 14, 2010 at 4:55 pm
Got it. Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply