Select Records For Current and Previous YTD 2

[larrydavid]

Hello,

I am currently using this where clause to pull records for 18-months from the current date:

convert(datetime, paiddate, 102) > dateadd(month, -18, convert(datetime, getDate(),102))

Now I am trying to apply the same logic to filter records for
the latest completed month (e.g., If August 15, 2011, then YTD – Current = Jan – Jul 2011).

And through latest completed month for the previous year (e.g., If August 15, 2011, then YTD – Previous = Jan – Jul 2010).

I've been trying to apply what I have for the 18-month filter but can't quite get it. I've tried searching for this and have been trying to work with something like this:

SELECT * FROM Test_Table 
WHERE DateColumn > DATEADD(year,-1,getdate())

Any help in nailing this down for the Current and Previous YTD would be greatly appreciated as always.

Thanks,
Larry

 

[Rhys666]

Not quite sure if its what you're after but the below should get you a DateTime 10 milliseconds from midnight for the last calender date of the previous month.I.E., running this today should return '2011-07-31 23:59:59.990'.

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, (DATEADD(DAY, -DATEPART(DAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP))), .9999999)

It should be a fairly simple job to extend this and get the start of the calendar month, 18 months prior to this date...

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[sabinUE]

You can use this dates in a where condition...

select getdate()
,dateadd(month, datediff(month, 0, getdate()), 0) /*2011 08 01*/
,dateadd(year, datediff(year, 0, getdate()), 0) /* 2011 01 01*/

,dateadd(year, datediff(year, 0, getdate())-1, 0) /* 2010 01 01*/
,dateadd(month, datediff(month, 0, getdate())-12 , 0) /*2010 08 01*/

 

[larrydavid]

Thank you everyone. Much obliged. Larry