Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Finding month/year start dates for previous year

Author  Topic 

offspring22
Starting Member

38 Posts

Posted - 2011-07-28 : 17:51:06
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

15732 Posts

Posted - 2011-07-28 : 18:06:05
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

38 Posts

Posted - 2011-07-28 : 18:59:15
Those 2 worked great, thanks!

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-28 : 19:33:10
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

38 Posts

Posted - 2011-07-29 : 10:51:51
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

38 Posts

Posted - 2011-07-29 : 12:45:05
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

52326 Posts

Posted - 2011-07-29 : 13:26:46
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

30421 Posts

Posted - 2011-07-29 : 15:36:27
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"
Go to Top of Page

offspring22
Starting Member

38 Posts

Posted - 2013-03-01 : 13:20:01
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

52326 Posts

Posted - 2013-03-01 : 13:33:18
[code]
select DATEADD(mm,DATEPART(mm,getdate())-1,DATEADD(yy,datediff(yy,0,getdate())-1,0))-1
[/code]

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

Go to Top of Page

offspring22
Starting Member

38 Posts

Posted - 2013-03-01 : 17:50:53
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-01 : 19:27:37
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

52326 Posts

Posted - 2013-03-02 : 02:42:58
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

30421 Posts

Posted - 2013-03-02 : 09:14:55
My code still work...
Just saying.


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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-02 : 09:41:10
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

38 Posts

Posted - 2013-03-04 : 10:52:48
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

38 Posts

Posted - 2013-03-04 : 11:06:43
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-04 : 12:12:11
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

30421 Posts

Posted - 2013-03-04 : 16:26:11
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"
Go to Top of Page
   

- Advertisement -