September 20, 2012 at 8:06 am
Hey guys,
Is there a static way to get the last day of the each month from the past quarter?
I know how this is done with hard code but I am wondering if there is a way to get this accomplished statically?
September 20, 2012 at 8:12 am
Not sure what you mean by "static" vs "hard coded".
I use nested DateAdd and DateDiff functions to get last and first days of various time periods. For example, find the number of months (DateDiff) from 1 Jan 1900 till today, and then add that (DateAdd) to 1 Jan 1900, and you get the first day of the current month. Use weeks instead of months, and you get the first day of the week. Subtract 1 day and you get the last day of the prior period (month or week). And so on.
That method is very fast and efficient.
You can also nest those. First day of the first week of the month? Nest the first of month inside the first of week, and there you go.
And so on.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 20, 2012 at 8:21 am
Here is what I meant:
We are going to be running a report at the end of a current quarter...And for my procedure I need to know what the last day of each month is from the prior quarter...So for example...We are going to run this report on October 1, 2012...I need the last day of each month from the prior quarter (7/31/12, 8/31/12, 9/30/12)...Hard coded this is what I have:
SET @EndOfMonth1OfQuarter = CONVERT(VARCHAR(25), DATEADD(dd,-62,'10/01/2012'),101)
SET @EndOfMonth2OfQuarter = CONVERT(VARCHAR(25), DATEADD(dd,-31,'10/01/2012'),101)
SET @EndOfMonth3OfQuarter = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY('10/01/2012')),'10/01/2012'),101)
But that is with the 10/01/2012 hard coded...
Since this report will be run Oct 1, Jan 1, Apr 1, and Jul 1, what I have wont work b/c of the different days in each month and plus you have to account for a leap year? So what I am asking is there a way to do this without using the hard coded date?
September 20, 2012 at 8:27 am
September 20, 2012 at 8:35 am
Take a look here. It should have everything you are asking about and then some. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 20, 2012 at 8:42 am
Using the info from the url Sean provided and making some tweaks I would suggest something like this:
declare @RunDate datetime = '2012-10-01';
select
dateadd(dd, -1, dateadd(mm, datediff(mm, 0, @RunDate) - 2, 0)),
dateadd(dd, -1, dateadd(mm, datediff(mm, 0, @RunDate) - 1, 0)),
dateadd(dd, -1, dateadd(mm, datediff(mm, 0, @RunDate), 0))
FYI, I wouldn't do it this way myself. I would use less than the first of each month (2012-08-01, 2012-09-01. and 2012-10-01) for the filter instead of less than or equal the last day of the month.
September 20, 2012 at 9:35 am
This code will work for any date within a quarter to find the end of month for the three months in the prior querter.
select
CurrDateTime,
PriorQtrMonth1 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-2,-1),
PriorQtrMonth2 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-1,-1),
PriorQtrMonth3 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3),-1)
from
( -- Test data
select CurrDateTime = getdate()union all
select CurrDateTime = '20120101'union all
select CurrDateTime = '20120401'union all
select CurrDateTime = '20120701'union all
select CurrDateTime = '20121001'union all
select CurrDateTime = '20121231'
) a
Results:
CurrDateTime PriorQtrMonth1 PriorQtrMonth2 PriorQtrMonth3
----------------------- ----------------------- ----------------------- -----------------------
2012-09-20 11:36:42.263 2012-04-30 00:00:00.000 2012-05-31 00:00:00.000 2012-06-30 00:00:00.000
2012-01-01 00:00:00.000 2011-10-31 00:00:00.000 2011-11-30 00:00:00.000 2011-12-31 00:00:00.000
2012-04-01 00:00:00.000 2012-01-31 00:00:00.000 2012-02-29 00:00:00.000 2012-03-31 00:00:00.000
2012-07-01 00:00:00.000 2012-04-30 00:00:00.000 2012-05-31 00:00:00.000 2012-06-30 00:00:00.000
2012-10-01 00:00:00.000 2012-07-31 00:00:00.000 2012-08-31 00:00:00.000 2012-09-30 00:00:00.000
2012-12-31 00:00:00.000 2012-07-31 00:00:00.000 2012-08-31 00:00:00.000 2012-09-30 00:00:00.000
(6 row(s) affected)
This code will work for any date within a quarter to find the start of month for the three months in the prior querter.
select
CurrDateTime,
PriorQtrMonth1 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-3,0),
PriorQtrMonth2 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-2,0),
PriorQtrMonth3 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-1,0)
from
( -- Test data
select CurrDateTime = getdate()union all
select CurrDateTime = '20120101'union all
select CurrDateTime = '20120401'union all
select CurrDateTime = '20120701'union all
select CurrDateTime = '20121001'union all
select CurrDateTime = '20121231'
) a
Results:
CurrDateTime PriorQtrMonth1 PriorQtrMonth2 PriorQtrMonth3
----------------------- ----------------------- ----------------------- -----------------------
2012-09-20 11:32:38.377 2012-04-01 00:00:00.000 2012-05-01 00:00:00.000 2012-06-01 00:00:00.000
2012-01-01 00:00:00.000 2011-10-01 00:00:00.000 2011-11-01 00:00:00.000 2011-12-01 00:00:00.000
2012-04-01 00:00:00.000 2012-01-01 00:00:00.000 2012-02-01 00:00:00.000 2012-03-01 00:00:00.000
2012-07-01 00:00:00.000 2012-04-01 00:00:00.000 2012-05-01 00:00:00.000 2012-06-01 00:00:00.000
2012-10-01 00:00:00.000 2012-07-01 00:00:00.000 2012-08-01 00:00:00.000 2012-09-01 00:00:00.000
2012-12-31 00:00:00.000 2012-07-01 00:00:00.000 2012-08-01 00:00:00.000 2012-09-01 00:00:00.000
(6 row(s) affected)
September 20, 2012 at 9:40 am
Michael Valentine Jones (9/20/2012)
This code will work for any date within a quarter to find the end of month for the three months in the prior querter.
selectCurrDateTime,
PriorQtrMonth1 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-2,-1),
PriorQtrMonth2 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-1,-1),
PriorQtrMonth3 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3),-1)
from
( -- Test data
select CurrDateTime = getdate()union all
select CurrDateTime = '20120101'union all
select CurrDateTime = '20120401'union all
select CurrDateTime = '20120701'union all
select CurrDateTime = '20121001'union all
select CurrDateTime = '20121231'
) a
Results:
CurrDateTime PriorQtrMonth1 PriorQtrMonth2 PriorQtrMonth3----------------------- ----------------------- ----------------------- -----------------------
2012-09-20 11:36:42.263 2012-04-30 00:00:00.000 2012-05-31 00:00:00.000 2012-06-30 00:00:00.000
2012-01-01 00:00:00.000 2011-10-31 00:00:00.000 2011-11-30 00:00:00.000 2011-12-31 00:00:00.000
2012-04-01 00:00:00.000 2012-01-31 00:00:00.000 2012-02-29 00:00:00.000 2012-03-31 00:00:00.000
2012-07-01 00:00:00.000 2012-04-30 00:00:00.000 2012-05-31 00:00:00.000 2012-06-30 00:00:00.000
2012-10-01 00:00:00.000 2012-07-31 00:00:00.000 2012-08-31 00:00:00.000 2012-09-30 00:00:00.000
2012-12-31 00:00:00.000 2012-07-31 00:00:00.000 2012-08-31 00:00:00.000 2012-09-30 00:00:00.000
(6 row(s) affected)
This code will work for any date within a quarter to find the start of month for the three months in the prior querter.
selectCurrDateTime,
PriorQtrMonth1 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-3,0),
PriorQtrMonth2 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-2,0),
PriorQtrMonth3 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-1,0)
from
( -- Test data
select CurrDateTime = getdate()union all
select CurrDateTime = '20120101'union all
select CurrDateTime = '20120401'union all
select CurrDateTime = '20120701'union all
select CurrDateTime = '20121001'union all
select CurrDateTime = '20121231'
) a
Results:
CurrDateTime PriorQtrMonth1 PriorQtrMonth2 PriorQtrMonth3----------------------- ----------------------- ----------------------- -----------------------
2012-09-20 11:32:38.377 2012-04-01 00:00:00.000 2012-05-01 00:00:00.000 2012-06-01 00:00:00.000
2012-01-01 00:00:00.000 2011-10-01 00:00:00.000 2011-11-01 00:00:00.000 2011-12-01 00:00:00.000
2012-04-01 00:00:00.000 2012-01-01 00:00:00.000 2012-02-01 00:00:00.000 2012-03-01 00:00:00.000
2012-07-01 00:00:00.000 2012-04-01 00:00:00.000 2012-05-01 00:00:00.000 2012-06-01 00:00:00.000
2012-10-01 00:00:00.000 2012-07-01 00:00:00.000 2012-08-01 00:00:00.000 2012-09-01 00:00:00.000
2012-12-31 00:00:00.000 2012-07-01 00:00:00.000 2012-08-01 00:00:00.000 2012-09-01 00:00:00.000
(6 row(s) affected)
That is exactly what I would want somewhat...How would get the last day of the PriorQtrMonth1 instead of first day?
September 20, 2012 at 9:50 am
asm1212 (9/20/2012)
Michael Valentine Jones (9/20/2012)
This code will work for any date within a quarter to find the end of month for the three months in the prior querter.
selectCurrDateTime,
PriorQtrMonth1 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-2,-1),
PriorQtrMonth2 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-1,-1),
PriorQtrMonth3 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3),-1)
from
( -- Test data
select CurrDateTime = getdate()union all
select CurrDateTime = '20120101'union all
select CurrDateTime = '20120401'union all
select CurrDateTime = '20120701'union all
select CurrDateTime = '20121001'union all
select CurrDateTime = '20121231'
) a
Results:
CurrDateTime PriorQtrMonth1 PriorQtrMonth2 PriorQtrMonth3----------------------- ----------------------- ----------------------- -----------------------
2012-09-20 11:36:42.263 2012-04-30 00:00:00.000 2012-05-31 00:00:00.000 2012-06-30 00:00:00.000
2012-01-01 00:00:00.000 2011-10-31 00:00:00.000 2011-11-30 00:00:00.000 2011-12-31 00:00:00.000
2012-04-01 00:00:00.000 2012-01-31 00:00:00.000 2012-02-29 00:00:00.000 2012-03-31 00:00:00.000
2012-07-01 00:00:00.000 2012-04-30 00:00:00.000 2012-05-31 00:00:00.000 2012-06-30 00:00:00.000
2012-10-01 00:00:00.000 2012-07-31 00:00:00.000 2012-08-31 00:00:00.000 2012-09-30 00:00:00.000
2012-12-31 00:00:00.000 2012-07-31 00:00:00.000 2012-08-31 00:00:00.000 2012-09-30 00:00:00.000
(6 row(s) affected)
This code will work for any date within a quarter to find the start of month for the three months in the prior querter.
selectCurrDateTime,
PriorQtrMonth1 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-3,0),
PriorQtrMonth2 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-2,0),
PriorQtrMonth3 = dateadd(mm,(datediff(QQ,0,CurrDateTime)*3)-1,0)
from
( -- Test data
select CurrDateTime = getdate()union all
select CurrDateTime = '20120101'union all
select CurrDateTime = '20120401'union all
select CurrDateTime = '20120701'union all
select CurrDateTime = '20121001'union all
select CurrDateTime = '20121231'
) a
Results:
CurrDateTime PriorQtrMonth1 PriorQtrMonth2 PriorQtrMonth3----------------------- ----------------------- ----------------------- -----------------------
2012-09-20 11:32:38.377 2012-04-01 00:00:00.000 2012-05-01 00:00:00.000 2012-06-01 00:00:00.000
2012-01-01 00:00:00.000 2011-10-01 00:00:00.000 2011-11-01 00:00:00.000 2011-12-01 00:00:00.000
2012-04-01 00:00:00.000 2012-01-01 00:00:00.000 2012-02-01 00:00:00.000 2012-03-01 00:00:00.000
2012-07-01 00:00:00.000 2012-04-01 00:00:00.000 2012-05-01 00:00:00.000 2012-06-01 00:00:00.000
2012-10-01 00:00:00.000 2012-07-01 00:00:00.000 2012-08-01 00:00:00.000 2012-09-01 00:00:00.000
2012-12-31 00:00:00.000 2012-07-01 00:00:00.000 2012-08-01 00:00:00.000 2012-09-01 00:00:00.000
(6 row(s) affected)
That is exactly what I would want somewhat...How would get the last day of the PriorQtrMonth1 instead of first day?
Look at the code posted. There are two samples posted. The first gets the last day of each month for the previous quarter, and the second gets the first day of each month for the previous quarter.
September 20, 2012 at 9:51 am
Dummy me...i didnt see that! Sorry about that
September 20, 2012 at 12:55 pm
GSquared (9/20/2012)
I use nested DateAdd and DateDiff functions to get last and first days of various time periods. For example, find the number of months (DateDiff) from 1 Jan 1900 till today, and then add that (DateAdd) to 1 Jan 1900, and you get the first day of the current month. Use weeks instead of months, and you get the first day of the week. Subtract 1 day and you get the last day of the prior period (month or week). And so on.
Not quite. This will work for MOST time periods, but WEEKS is one exception. 1900-01-01 falls on a Monday, so adding weeks to it will also give you a Monday date instead of the Sunday that is usually considered the first day of the week in the US.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 20, 2012 at 2:18 pm
drew.allen (9/20/2012)
GSquared (9/20/2012)
I use nested DateAdd and DateDiff functions to get last and first days of various time periods. For example, find the number of months (DateDiff) from 1 Jan 1900 till today, and then add that (DateAdd) to 1 Jan 1900, and you get the first day of the current month. Use weeks instead of months, and you get the first day of the week. Subtract 1 day and you get the last day of the prior period (month or week). And so on.Not quite. This will work for MOST time periods, but WEEKS is one exception. 1900-01-01 falls on a Monday, so adding weeks to it will also give you a Monday date instead of the Sunday that is usually considered the first day of the week in the US.
Drew
Yeah, there's more to it than I ended up having time to post. Emergency meetings do that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply