Solved

Help constructing a T-SQL statement

Posted on 2011-03-23
7
299 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

631 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