| Author |
 Topic  |
|
|
offspring22
Starting Member
36 Posts |
 Posted - 07/28/2011 : 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
USA
15568 Posts |
Posted - 07/28/2011 : 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 |
 |
|
|
offspring22
Starting Member
36 Posts |
Posted - 07/28/2011 : 18:59:15
|
Those 2 worked great, thanks!
One more.... how about yesterday's date.... last year? |
|
|
|
robvolk
Most Valuable Yak
USA
15568 Posts |
Posted - 07/28/2011 : 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. |
|
|
|
offspring22
Starting Member
36 Posts |
Posted - 07/29/2011 : 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. |
|
|
|
offspring22
Starting Member
36 Posts |
Posted - 07/29/2011 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 07/29/2011 : 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/
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 07/29/2011 : 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" |
Edited by - SwePeso on 07/29/2011 15:40:00 |
|
|
|
offspring22
Starting Member
36 Posts |
Posted - 03/01/2013 : 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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 03/01/2013 : 13:33:18
|
select DATEADD(mm,DATEPART(mm,getdate())-1,DATEADD(yy,datediff(yy,0,getdate())-1,0))-1
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
offspring22
Starting Member
36 Posts |
Posted - 03/01/2013 : 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. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1743 Posts |
Posted - 03/01/2013 : 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 ThenDATEADD(dd,DATEDIFF(dd,0,DATEADD(yy,-1,DATEADD(dd,-1,GETDATE()))),0) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 03/02/2013 : 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/
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 03/02/2013 : 09:14:55
|
My code still work...
Just saying.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
James K
Flowing Fount of Yak Knowledge
1743 Posts |
Posted - 03/02/2013 : 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 :) |
|
|
|
offspring22
Starting Member
36 Posts |
Posted - 03/04/2013 : 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....?
|
|
|
|
offspring22
Starting Member
36 Posts |
Posted - 03/04/2013 : 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.... |
|
|
|
James K
Flowing Fount of Yak Knowledge
1743 Posts |
Posted - 03/04/2013 : 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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 03/04/2013 : 16:26:11
|
Create a new function using this codeCREATE 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 codeDECLARE @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 |
|
|
| |
Topic |
|
|
|
Finding month/year start dates for previous year
