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.
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 |
|
|
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? |
|
|
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. |
|
|
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. |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-29 : 15:36:27
|
Shorter. And simpler...declare @dt date = '20110729'--SwePesoSELECT 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" |
|
|
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? |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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 ThenDATEADD(dd,DATEDIFF(dd,0,DATEADD(yy,-1,DATEADD(dd,-1,GETDATE()))),0) |
|
|
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 beSELECT DATEADD(mm,MONTH(GETDATE()-1)-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()-1)-1,0))------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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" |
|
|
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 :) |
|
|
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 datetimeset @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 enterdeclare @mydate datetimeset @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
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.... |
|
|
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. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-03-04 : 16:26:11
|
Create a new function using this codeCREATE FUNCTION dbo.fnYesterdayOneYearAgo( @dt DATE)RETURNS DATEASBEGIN 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 SwePesoFROM @Sample; N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
|