How to calc YTD for previous year

[bustercoder]

Hello, I have this function I use to grab dates for the current year-to-date (From 1/1/2007 to current day):

RMTrxTemp.DOCDATE >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()),0) AND DOCDATE < DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()),0)

I'm having some trouble trying to figure out how to do exactly this but for the previous year. Could someone please show me? I thought it would be easy but...

Thanks,
Buster

 

[cfStarlight]

Can't say I've used that method, but what about

DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE())-1, 0), 
DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()),0))

 

[bustercoder]

Thanks cf!
I'll give it a try and let you know!
BC

 

[bustercoder]

Hello cf,

Maybe I didn't explain it very well or I'm not understanding your suggestion. With my first function, it returns everything from '2007-01-01' to current day. I'm trying to get this exact same thing to happen, but for the previous year. So, if I have a date for a record, say for instance '2007-02-03' I need to pull the same exact date for previous year, so the date would be '2006-02-03'. It didn't seem to be working so I thought I'd ask again.

Thanks,
BC

 

[cfStarlight]

BC,

Maybe I didn't understand your request.

It sounds like you have a query that returns records from:
The first date of this year: 2007-01-01
To the current date: 2007-04-28

I thought you wanted to modify the query so it returns records records from:
The first date of last year: 2006-01-01
To this date last year: 2006-04-28

So I only posted the modified functions that would return the first date of last year and this date last year. Is that what you're looking for?

SELECT
DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE())-1, 0) 
   AS FirstDateOfLastYear,
DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()),0))   
   AS ThisDateLastYear

 

[bustercoder]

Hi cf,

Regardless of the year, the starting date should always be Jan 1 with ending date as the current date. So, with this function can I assume that Jan 1 for any year is in fact the starting date?

Thanks,
BC

 

[cfStarlight]

Like I said before, I can't say I've used that method. But assuming your original function always returns Jan 1st.

DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()),0)

Then modifying it to deduct X number of years should always return Jan 1 too.

-- deduct X number of years
DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE())- [b]X[/b],  0)

If you don't feel comfortable with it you could always use

--- Jan 1st of @someYear
CAST(@someYear +'-01-01' AS datetime)