SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Finding month/year start dates for previous year
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

offspring22
Starting Member

36 Posts

Posted - 07/28/2011 :  17:51:06  Show Profile  Reply with Quote
So I need create a query that creates some statistics for this year, and last year, specifically month to date, and year to date, that will run on a daily basis.

I found current years start date, and current months start date help online which has worked, but I haven't been able to find anything to help with last year start dates, and current month last year start dates.

Any one solved this problem?

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 07/28/2011 :  18:06:05  Show Profile  Visit robvolk's Homepage  Reply with Quote
SELECT DATEADD(year, DATEDIFF(year, 0, GETDATE())-1, 0) StartofLastYear,
DATEADD(month, DATEPART(month, GETDATE())-1, DATEADD(year, DATEDIFF(year, 0, GETDATE())-1, 0) StartThisMonthofLastYear
Go to Top of Page

offspring22
Starting Member

36 Posts

Posted - 07/28/2011 :  18:59:15  Show Profile  Reply with Quote
Those 2 worked great, thanks!

One more.... how about yesterday's date.... last year?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 07/28/2011 :  19:33:10  Show Profile  Visit robvolk's Homepage  Reply with Quote
DATEADD(day, DATEPART(y, GETDATE())-1, DATEADD(year, DATEDIFF(year, 0, GETDATE())-1, 0)

I haven't tested this but I think it's correct.
Go to Top of Page

offspring22
Starting Member

36 Posts

Posted - 07/29/2011 :  10:51:51  Show Profile  Reply with Quote
Missed a close bracket at the end, but with it, it gave me todays date last year. I added a -1 and it seems to work.

select DATEADD(day, DATEPART(y, GETDATE())-1, DATEADD(year, DATEDIFF(year, 0, GETDATE())-1, 0)-1)

Thank you sir, you're a gentleman, and a scholar.
Go to Top of Page

offspring22
Starting Member

36 Posts

Posted - 07/29/2011 :  12:45:05  Show Profile  Reply with Quote
Ok, one last one I think...

The end of last month last year.... so today I'd need it to return 6/30/2010
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/29/2011 :  13:26:46  Show Profile  Reply with Quote
select DATEADD(mm,MONTH(GETDATE())-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0))-1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/29/2011 :  15:36:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Shorter. And simpler...
declare @dt date = '20110729'
--SwePeso
SELECT	DATEADD(YEAR, DATEDIFF(YEAR, '19010101', @dt), '19000101') AS StartofLastYear,
	DATEADD(MONTH, DATEDIFF(MONTH, '19010101', @dt), '19000101') AS StartThisMonthofLastYear,
	DATEADD(DAY, DATEDIFF(DAY, '19050101', @dt), '19040101') AS YesterdayPreviousYear


N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 07/29/2011 15:40:00
Go to Top of Page

offspring22
Starting Member

36 Posts

Posted - 03/01/2013 :  13:20:01  Show Profile  Reply with Quote
quote:
Originally posted by offspring22

Missed a close bracket at the end, but with it, it gave me todays date last year. I added a -1 and it seems to work.

select DATEADD(day, DATEPART(y, GETDATE())-1, DATEADD(year, DATEDIFF(year, 0, GETDATE())-1, 0)-1)

Thank you sir, you're a gentleman, and a scholar.



To bump an old thread... This has been working perfectly for me until today! It doesn't work with the leap year unforunately - it's pulling up the 28th, which looking at the code makes sense. Any idea how to avoid the leap year issue in the future?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/01/2013 :  13:33:18  Show Profile  Reply with Quote

select DATEADD(mm,DATEPART(mm,getdate())-1,DATEADD(yy,datediff(yy,0,getdate())-1,0))-1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

offspring22
Starting Member

36 Posts

Posted - 03/01/2013 :  17:50:53  Show Profile  Reply with Quote
Is that yesterday, last year? Or last day of last month, last year? Replacing the getdates with a date, and it always gives me the end of the previous month. I need it to give me yesterday, last year.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3329 Posts

Posted - 03/01/2013 :  19:27:37  Show Profile  Reply with Quote
Assuming the third column in the following is what you are looking for:
--  Today           Yesterday   One year ago yesterday
--  2013-03-01       2013-02-28         2012-02-28

--  Today           Yesterday   One year ago yesterday
--  2012-03-01       2012-02-29         2011-02-28
--  2012-02-29       2012-02-28         2011-02-28
Then
DATEADD(dd,DATEDIFF(dd,0,DATEADD(yy,-1,DATEADD(dd,-1,GETDATE()))),0)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/02/2013 :  02:42:58  Show Profile  Reply with Quote
quote:
Originally posted by offspring22

Is that yesterday, last year? Or last day of last month, last year? Replacing the getdates with a date, and it always gives me the end of the previous month. I need it to give me yesterday, last year.


then it should be

SELECT DATEADD(mm,MONTH(GETDATE()-1)-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()-1)-1,0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/02/2013 :  09:14:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
My code still work...
Just saying.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3329 Posts

Posted - 03/02/2013 :  09:41:10  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

My code still work...
Just saying.


N 56°04'39.26"
E 12°55'05.63"


If I understood OP's request, when current date is March 1, 2013, he wanted to get "yesterday a year ago" as February 28, 2012 rather than February 29, 2012. In other words, he wanted, "take yesterday and then go back a year ago" rather than "take a year ago and then go back one day from there". Now, why it has to be that way... who am I to question that :)
Go to Top of Page

offspring22
Starting Member

36 Posts

Posted - 03/04/2013 :  10:52:48  Show Profile  Reply with Quote
At March 1st, 2013, I want it to return Feb 29th, 2012. Maybe It's the way I'm testing, but I'm not getting the right response for any of the above.... Example:

James K - I'm inputting the following:

declare @mydate datetime
set @mydate = '3/1/2013'
SELECT DATEADD(dd,DATEDIFF(dd,0,DATEADD(yy,-1,DATEADD(dd,-1,@mydate))),0)

I get 2012-02-28 00:00:00.000 returned.

Visakh16, I enter

declare @mydate datetime
set @mydate = '3/1/2013'
SELECT DATEADD(mm,MONTH(@mydate-1)-1,DATEADD(yy,DATEDIFF(yy,0,@mydate-1)-1,0))

I get 2012-03-01 00:00:00.000 returned.

SwePeso:

Yours appears to work, except in leap years itself. For example with a date of '03/1/2016' I get '03/1/2015' returned (instead of 02/28/2015', so really it'll cause me the same issue in 3 years....

Is there an issue with my testing? Or....?

Go to Top of Page

offspring22
Starting Member

36 Posts

Posted - 03/04/2013 :  11:06:43  Show Profile  Reply with Quote
Might be worth mentioning we've upgraded to 2008 since the original post, but I don't think anything has changed that would effect this....
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3329 Posts

Posted - 03/04/2013 :  12:12:11  Show Profile  Reply with Quote
I don't think it is the upgrade to 2008 that is affecting the results. In the query I posted, I had also indicated what the outcome would be. Another possibility (and I am not sure that this is what you are looking for either, especially the third row), would be the following:
SELECT DATEADD(dd,-1,DATEADD(yy,-1,GETDATE()))


That would give the following:

--  Today           One year ago yesterday
--  2013-03-01              2012-02-29

--  Today           One year ago yesterday
--  2012-03-01              2011-02-28
--  2012-02-29              2011-02-27
Perhaps if you can make a table similar to what I am doing here and post your desired results, someone would be able to provide more accurate response.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 03/04/2013 :  16:26:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Create a new function using this code
CREATE FUNCTION dbo.fnYesterdayOneYearAgo
(
	@dt DATE
)
RETURNS DATE
AS
BEGIN
	RETURN	(
			SELECT  MAX(dt)
			FROM	(
					VALUES	(DATEADD(YEAR, -1, DATEADD(DAY, -1, @dt))),
						(DATEADD(DAY, -1, DATEADD(YEAR, -1, @dt)))
				) AS d(dt)
		)
END
and then verify the results using this code
DECLARE	@Sample TABLE
	(
		dt DATE
	);

INSERT	@Sample
	(
		dt
	)
VALUES	('20120229'),
	('20120301'),
	('20130228'),
	('20130301'),
	('20140228'),
	('20140301'),
	('20121231'),
	('20131231'),
	('20141231');

SELECT	dt,
	dbo.fnYesterdayOneYearAgo(dt) AS SwePeso
FROM	@Sample;



N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 03/04/2013 16:26:43
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000