Basic SQL Query Question....

Can someone please tell me how I do the following...
I have a variable @.Date which is a year... EG: 2005 what I want to be able
to do is return all the records found in a table which match the @.Date
variable!
E.G:
IF @.Date = 2005
All the records added to the database which match the year 2005 from the
field DateAdded are returned!
See example below!
Thanks for any help!
CREATE PROCEDURE dbo.GetVVIssue
(
@.FileDescription nvarchar (255),
@.Date Datetime
)
AS
SELECT fileID, FileName, FileDescription, DateAdded
FROM
tblFiles
WHERE FileDescription = @.FileDescription AND DateAdded = @.Date
ORDER BY DateAdded ASC
GOTim
What is a datatype of the Dateadded column?
If you store into a variable @.date only a year why you are defined it as
datetime
declare @.date int --or char(4)
SELECT fileID, FileName, FileDescription, DateAdded
FROM
tblFiles
WHERE FileDescription = @.FileDescription AND DateAdded = @.Date
ORDER BY DateAdded ASC
"Tim::.." <myatix_at_hotmail.com> wrote in message
news:BF72BABE-2F2E-4CE4-87A2-F015CE3C67E9@.microsoft.com...
> Can someone please tell me how I do the following...
> I have a variable @.Date which is a year... EG: 2005 what I want to be able
> to do is return all the records found in a table which match the @.Date
> variable!
> E.G:
> IF @.Date = 2005
> All the records added to the database which match the year 2005 from the
> field DateAdded are returned!
> See example below!
> Thanks for any help!
>
> CREATE PROCEDURE dbo.GetVVIssue
> (
> @.FileDescription nvarchar (255),
> @.Date Datetime
> )
> AS
> SELECT fileID, FileName, FileDescription, DateAdded
> FROM
> tblFiles
> WHERE FileDescription = @.FileDescription AND DateAdded = @.Date
> ORDER BY DateAdded ASC
> GO
>|||DateAdded has a datatype of DateTime!
But how do I return all the records found in a table which match the @.Date
variable in the DateAdded column?
Thanks
"Uri Dimant" wrote:

> Tim
> What is a datatype of the Dateadded column?
> If you store into a variable @.date only a year why you are defined it as
> datetime
> declare @.date int --or char(4)
> SELECT fileID, FileName, FileDescription, DateAdded
> FROM
> tblFiles
> WHERE FileDescription = @.FileDescription AND DateAdded = @.Date
> ORDER BY DateAdded ASC
>
>
> "Tim::.." <myatix_at_hotmail.com> wrote in message
> news:BF72BABE-2F2E-4CE4-87A2-F015CE3C67E9@.microsoft.com...
>
>|||Tim
SELECT fileID, FileName, FileDescription, DateAdded
FROM
tblFiles
WHERE FileDescription = @.FileDescription AND YEAR(DateAdded) = YEAR(@.Date)
ORDER BY DateAdded ASC
Note: An optimizer won't use an index on DateAdded column.
"Tim::.." <myatix_at_hotmail.com> wrote in message
news:66F29AF7-64A0-49BD-AEC2-440F91648DB7@.microsoft.com...
> DateAdded has a datatype of DateTime!
> But how do I return all the records found in a table which match the @.Date
> variable in the DateAdded column?
> Thanks
>
> "Uri Dimant" wrote:
>
able
the|||In your example @.date is a DATETIME not a numeric so it cannot be equal
to the value 2005. It could be 2005-01-01 00:00:00.000 or 2005-12-31
00:00:00.000 or some other date but if you try to pass it the numeric
value 2005 then @.date will actually take the value of a date in 1905
because the numeric is interpreted as X number of days since
1900-01-01.
What you need is a range query, such as:
WHERE dateadded >= '20050101'
AND dateadded < '20060101'
You could parameterize this by passing the date as '20050101':
WHERE dateadded >= @.year_start_date
AND dateadded < DATEADD(YEAR,1,@.year_start_date)
or you could obviously pass both the start and end dates as parameters.
If you really eanted to pass just a numeric value then change the
parameter to a numeric (@.year) and try this:
WHERE dateadded >= DATEADD(YEAR,@.year-2000,'20000101')
AND dateadded < DATEADD(YEAR,@.year-1999,'20000101')
Hope this helps.
David Portas
SQL Server MVP
--