|
You can use EOMONTH
https://docs.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql
|
|
|
|
|
|
Thanks for your valued comments.
Yes EOMONTH can be used instead but you might have noticed in the introduction I explained that this tip is handy when you are working on versions prior to SQL Server 2012 since EOMONTH is not (backward) compatible with versions before SQL Server 2012.
Secondly in this tip I intended to develop interest for beginners towards date manipulations by showing them how different date functions can be used to get more interesting information about dates.
Once again thank you for your precious feedback.
|
|
|
|
|
While DateAdd is handy, your code may not solve the all you problems when you use different data types such as Date, DateTime2 and DateTimeOffset. Simply, you can's use @var + 1 to increment the date and must use DateAdd. Also, you may have issues when subtracting a month from that has fewer days than the current month. I believe this code will solve those problems.
select d.today
, [First Day of Current Month] = dateadd(day, -day(d.today) + 1, d.today)
, [Last Day of Current Month] = dateadd(day, -1, dateadd(month, 1, dateadd(day, -day(d.today) + 1, d.today)))
, [First Day of Prior Month] = dateadd(day, -day(dateadd(month, -1, d.today)) + 1, dateadd(month, -1, d.today))
, [Last Day of Prior Month] = dateadd(day, -day(d.today), d.today)
from( select today = convert(date, getdate()) ) d
|
|
|
|
|
Hi,
Thanks a lot for your time to read the tip and not only providing a very positive feedback but also sharing your precious code.
If you ask me about the scope of this code (attached with the tip) then I would say this tip and the related code is limited to DateTime data type as the intention was to give some idea of how to manipulate date functions to the beginners (not experienced ones like you).
In order to keep the discussion more interesting I am happy to answer one of the questions raised at the end of your comments about using @var+1.
If we look closely how the function to subtract all days work in the tip it has an answer in it. The logic is to subtract all days of the current date as shown:
-- Subtract all days of current month
DECLARE @MonthFirstDay DATETIME =_
(SELECT CONVERT(CHAR(15),DATEADD(DD,-DAY(GETDATE()),GETDATE()),106))
So think of this as GETDATE() can be any date but if you subtract any date with the same (any) date you can always get the first day by adding 1 to the result and this is true for any condition.
Further more, no solution is perfect there is always room for improvement and people like you are helping the community by reviews and feedbacks.
|
|
|
|
|
It's true what you say about DateTime data type as it is stored as a decimal datatype and math addition/subtraction work on it. By always using DateAdd, you will be able to use all date data types.
On another note, there's no need to use a select when setting/initializing a variable
DECLARE @MonthFirstDay DATETIME = (SELECT CONVERT(CHAR(15),DATEADD(DD,-DAY(GETDATE()),GETDATE()),106)) ...will behave exactly the same as...
DECLARE @MonthFirstDay DATETIME = CONVERT(CHAR(15),DATEADD(DD,-DAY(GETDATE()),GETDATE()),106)
|
|
|
|
|
Much apprciated!
Thanks for improving the code. 
I think the idea behind using SELECT is to show the beginners how to construct the basic function (which I am considering to add in the next revision):
SELECT CONVERT(CHAR(15),DATEADD(DD,-DAY(GETDATE()),GETDATE()),106) -- Step-1
-- 31 Aug 2017 -- Checking output first
Then assign it to a variable for further work
DECLARE @MonthFirstDay DATETIME = (SELECT CONVERT(CHAR(15),DATEADD(DD,-DAY(GETDATE()),GETDATE()),106)) -- Step-2
However, I agree with you SELECT in the above code is optional and can be omitted.
|
|
|
|
|
Hi, so there seems to be quite a bit of extra calculation steps that do not seem to be necessary here. From my perspective, it seems like it would be most appropriate to use the first date of the current month as a seed, then you can build the other dates from that seed date...
Using a CTE you can keep the first day of the current month in memory and build out from there.
; With Dates As (
Select Convert(date, Concat(Month(GetDate()), '/1/', Year(GetDate()))) As [Current]
)
Select [Current] As MonthFirstDay
, DateAdd(day, -1, DateAdd(month, 1, [Current])) MonthLastDay
, DateAdd(month, -1, [Current]) As PrevMonthFirstDay
, DateAdd(day, -1, [Current]) As PrevMonthLastDay
, DateAdd(month, 1, [Current]) As NextMonthFirstDay
, DateAdd(day, -1, DateAdd(month, 2, [Current])) As NextMonthLastDay
From Dates
Or, if you do not wish to use a CTE
Select d.[Current] As MonthFirstDay
, DateAdd(day, -1, DateAdd(month, 1, d.[Current])) MonthLastDay
, DateAdd(month, -1, d.[Current]) As PrevMonthFirstDay
, DateAdd(day, -1, d.[Current]) As PrevMonthLastDay
, DateAdd(month, 1, d.[Current]) As NextMonthFirstDay
, DateAdd(day, -1, DateAdd(month, 2, d.[Current])) As NextMonthLastDay
From (
Select Convert(date, Concat(Month(GetDate()), '/1/', Year(GetDate()))) As [Current]
) d
|
|
|
|
|
Thanks for sharing your code and feedback.
Now I would like to share some more background about this tip.
This tip was written keeping four things in mind:
1. First of all this is a tip for the beginners (not for experts like you )
2. Secondly this was shared keeping in mind that four different but independent functions can replace these scripts to avoid coding repetition so seed cannot be used as in your case the code always rely on calculating the first day of current month and then referring to it afterwards (nevertheless it is an excellent solution)
3. Thirdly this script was instantly written the time it was required and worked very fine despite the fact I knew far more better solutions are there (but it is nice to share your work just like you shared with us)
4. The purpose was to walk through the steps involved to build all these methods starting from the basics so this is rather simpler than using CTE or derived table (plus you might have noticed that I have not used any other string function like concat).
The next revision of the article (soon) is going to be focussed on constructing the method from the very basic step as mentioned above plus I am also considering to add your precious code portion (with your permission) to give alternative approach.
Thank you very much for your time and effort.
|
|
|
|
|
Thank you for your feedback.
I understand in greater detail what you were working to accomplish here.
Please feel free to use my code as needed.
When I teach my students, I typically try to show them to build off of a common point, which is why I mentioned putting forward the first date of the month (which can be stored in a variable instead of just in a CTE or a derived table).
Also, if there is no desire to work with string concatenation (which can be expensive) the following can be used instead:
Convert(date, DateAdd(day, -Day(GetDate()) + 1, GetDate()))
I suppose building each of them in turn from the ground up, (personally) I would have only introduced the DateAdd() and Day() methods to cut down on what I needed to explain to them. Only because introducing Convert went over some of my students heads when dealing with the different Char() values and the additional style parameter of the Convert method.
Declare @MonthFirstDay date = DateAdd(DD, -Day(GetDate()) + 1, GetDate())
Select @MonthFirstDay as MonthFirstDay
Declare @MonthLastDay date = DateAdd(MM, 1, GetDate())
Set @MonthLastDay= DateAdd(DD, -Day(@MonthLastDay), @MonthLastDay)
Select @MonthLastDay as MonthLastDay
Declare @PrevMonthFirstDay date = DateAdd(MM, -1, GetDate())
Set @PrevMonthFirstDay= DateAdd(DD, -Day(@PrevMonthFirstDay) + 1, @PrevMonthFirstDay)
Select @PrevMonthFirstDay as PrevMonthFirstDay
Declare @PrevMonthLastDay date= DateAdd(DD, -Day(GetDate()), GetDate())
Select @PrevMonthLastDay as PrevMonthLastDay
But that is just me. I appreciate your article and have already shared it with my students to help them in their own SQL journey's
|
|
|
|
|
Thanks for your valued further feedback.
Yes, it is a nice approach (what you mentioned) for the beginners/students.
I am considering a revised version more seriously now to not only include your ideas and approach but also what I actually wanted to convey through this article.
I hope our conversation may help others to understand (particularly beginners) how should they begin their SQL Journey taking a simple example of writing code for first and last day of the current and previous month.
As you explained that you start off with a common building point as a reference as obvious from your code and I like it, while I tend to convert the complexity into simple stable piece of code that means focussing on purpose to build something from very basic thing and then adding more to it until requirement is met (for students).
Thank you once again for sharing your valued thoughts.
|
|
|
|
|
declare @date date = (SELECT CONVERT(DATE, DATEADD(S, -1, DATEADD(MM, DATEDIFF(M, 0, GETDATE()) + 1,0))));
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Thanks for sharing your code.
The purpose of the tip was to understand how to construct these methods starting from the basic steps to be ultimately used as four different independent functions to be reused.
I am considering a revision of the article soon to highlight the idea mentioned above especially for the beginners (not at all for experts) as this is a practice code written keeping in mind that beginners might pick it and then customise it according to their requirements.
I know there may be hundreds of better methods that can be used but as long as this works and serving the purpose it is fine to share with others.
Thank you once again for your time and effort.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
