Select data for a year

Question

Post reply

Select data for a year

omlac

Old Hand

Points: 359
More actions
January 19, 2011 at 7:06 am

#243827

im using the query below to select data for a period of one year using DateTimeIn as the date parameter. if say we are in Jan 2011, i would leave out any data for Jan 2010. does anyone know what i must do to achieve this.
The query below has a problem when i add the following part
and (month(DateTimeIn) <> month(getdate()) and year(DateTimeIn) <> year(getdate())-1);
select *,'Enq' as Dtype from vwQV_Enquiry where ProformaCount = 0 and DateTimeIn >= dateadd(month,-12,getdate()) and PI not in (17,18)and (month(DateTimeIn) <> month(getdate()) and year(DateTimeIn) <> year(getdate())-1);
Thanks

Viewing 6 posts - 1 through 5 (of 5 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: 76985 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: 359 More actions · Answer 4

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

omlac Old Hand Points: 359 More actions · Answer 5

@SSCrazy

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