Select Records based on Year

Question

Post reply

Select Records based on Year

Chris.Hubbard4U

Ten Centuries

Points: 1020
More actions
August 17, 2015 at 6:14 am

#302027

DECLARE @Date
SET @Date = 2012
DECLARE @Year int
SET @Year = (SELECT DATEPART(yyyy,@Date))
SELECT @Year AS Year
--SELECT *
-- FROM [Orders].[dbo].[Orders] od
-- WHERE .Orderl_Date < @Date;
----WHERE DATEPART(yyyy,@Date)= @Year
----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Duplicate post. No replies please

Replies to http://www.sqlservercentral.com/Forums/Topic1712220-3077-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

you want to leave the column alone, without functions or conversions, so you can use any existing indexes.

instead, generate the dates the date values should be in between:

DECLARE @Date datetime --use a proper date!

SET @Date =getdate() --august 17 of this year

SELECT *

FROM [Orders].[dbo].[Orders] od

WHERE Orderl_Date >= DATEADD(yy, DATEDIFF(yy,0,@Date), 0) --jan 1 of the year of the date param passed.

AND Orderl_Date < DATEADD(yy,1,DATEADD(yy, DATEDIFF(yy,0,@Date), 0)) --add one year: jan 1 of following year

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Chris.Hubbard4U (8/17/2015)
DECLARE @Date
SET @Date = 2012
DECLARE @Year int
SET @Year = (SELECT DATEPART(yyyy,@Date))
SELECT @Year AS Year
--SELECT *
-- FROM [Orders].[dbo].[Orders] od
-- WHERE .Orderl_Date < @Date;
----WHERE DATEPART(yyyy,@Date)= @Year
----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear

Here's one way:

declare @Year int = 2012

declare @StartDate date = datefromparts(@Year, 1, 1)

declare @EndDate date = datefromparts(@Year + 1, 1, 1)

select

from

where [date] >= @StartDate and [date] < @EndDate

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

GilaMonster (8/17/2015)
Duplicate post. No replies please
Replies to http://www.sqlservercentral.com/Forums/Topic1712220-3077-1.aspx

Oops, didn't see this in time.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Chris.Hubbard4U Ten Centuries Points: 1020 More actions · Answer 5

GilaMonster (8/17/2015)
Duplicate post. No replies please
Replies to http://www.sqlservercentral.com/Forums/Topic1712220-3077-1.aspx

Sorry it was an accident.:blush: