Solved

Help constructing a T-SQL statement

Posted on 2011-03-23
7
283 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 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

13 Experts available now in Live!

Get 1:1 Help Now