Solved

Help constructing a T-SQL statement

Posted on 2011-03-23
7
285 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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35202109


declare @Param_MyYear integer

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

Expert Comment

by:ewangoya
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 142

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 60

Expert Comment

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

943 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now