Solved

Help constructing a T-SQL statement

Posted on 2011-03-23
7
287 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
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.

 
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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore 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.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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