Select data for a year

Question

Post reply

Select data for a year

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

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

RP_DBA SSCertifiable Points: 5116 More actions · Answer 1

Unless I'm misreading it, it seems like you only want to go back 11 months when filtering on DateTimeIn.

DECLARE @StartDate DATETIME

SET @StartDate = CAST(CAST(Month(GETDATE()) AS CHAR(2)) + '/1/' + CAST(YEAR(GETDATE()) AS CHAR(4)) AS DATETIME)

SELECT DATEADD(MONTH,-11,@StartDate)

_____________________________________________________________________
- Nate

@nate_hughes

drew.allen SSC Guru Points: 77014 More actions · Answer 2

You should use DateDiff() instead. Converting datetime data back and forth between char data is inefficient.

WHERE DateDiff(Month, DateTimeIn, GetDate()) < 12

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

It is best to do the selection on a date range, and not use a selection where your column is in a function call. If your column is used in a function call, the query will not be able to use an index on the column.

select

*

from

MyTable

where

-- Select all data for prior 11 months and current month

-- GE 11th prior month

DateTimeIn >= dateadd(mm,datediff(mm,0,getdate())-11,0)and

-- Before start of next month

DateTimeIn < dateadd(mm,datediff(mm,0,getdate())+1,0)

omlac Old Hand Points: 389 More actions · Answer 4

thansk unfortunately i dint want to use cursors. thanks for your help, i appreciate it.

omlac Old Hand Points: 389 More actions · Answer 5

@SSCrazy

thansk it worked, and thanks to all you guys you helped me.