Solved

Help constructing a T-SQL statement

Posted on 2011-03-23
7
291 Views
Last Modified: 2012-08-13
Hi - I'd like to pass a year parameter to a select statement and grab all record found in the table for that year.  Something like

@Param_MyYear

AS
BEGIN
    SELECT * FROM table WHERE MyDateTimeColumn ???????????
END

But I don't know how to construct my WHERE clause.  

Can someone help?
0
Comment
Question by:cdemott33
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35202109


declare @Param_MyYear integer

AS
BEGIN
    SELECT * FROM table WHERE DATEPART(YY, MyDateTimeColumn) = Param_MyYear
END
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35202118
Procedure param, not decalared
@Param_MyYear integer

AS
BEGIN
    SELECT * FROM table WHERE DATEPART(YY, MyDateTimeColumn) = Param_MyYear 
END

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35202119
no problem:
@Param_MyYear int --- assumed 4 digits
AS
BEGIN
 DECLARE @start_dt datetime
 SET @start_dt = CONVERT(datetime, cast(@Param_MyYear as varchar(4)) + '-01-01', 120)
 SELECT * 
   FROM table 
  WHERE MyDateTimeColumn >= @start_dt
    AND MyDateTimeColumn < DATEADD(YEAR, 1, @start_dt)
END

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:chapmandew
ID: 35202124
Do you want the query to run fast or slow?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35202129
note: while the above suggestions with DATEPART() will actually work, they would not be able to use a index on the MyDateTimeColumn ...
just so you know
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 35202136
yes, use angeliii's example.  It is the proper way to do this.
0
 

Author Closing Comment

by:cdemott33
ID: 35202239
Thank you all.  This worked!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question