Hi Experts !
I am trying to get the last date of the every month data on range basis but unable to get it . When I use group by function the dates not showing correctly. Required to display the data for Sep, Oct , Nov last day data.
Please help me out
DECLARE @Startdate DATETIME
DECLARE @EndDate DATETIME
SET @Startdate =DATEADD(mm,DATEDIFF(mm,0,'1-SEP-19'),0) --- Run on any date will GO First date of the month.
SET @EndDate = dateadd(month,1+datediff(month,0,'11-NOV-19'),-1) --- Run on any date will GO End date of the month.
SELECT @Startdate 'startdate',
@EndDate 'enddate',
Sales
FROM Table WHERE DATEADD(mm,DATEDIFF(mm,0,START_DATETIME),0) >= @Startdate
AND dateadd(month,1+datediff(month,0,START_DATETIME),-1)<= @EndDate
Sorry ! I mean When I select @ startdate = 1-Sep-19 and @EndDate = 11-Nov-19 it showing me the daily data between the give date range.
I need the data and date last date of every month like
30-Sep-19 Last day value
31-Oct-19 Last day Value
30-Nov19 Last day value
Just to confirm - given an end date of 2019-11-19 (or any day in that month) you want the last day of the past 3 months only? If you have an end date given as 2019-10-20 do you want the start date to be 2019-08-01?
If so - then this should work:
Declare @endDate datetime = '2019-11-19';
Declare @Startdate datetime = dateadd(month, datediff(month, 0, @endDate) - 3, 0);
Select @Startdate, @EndDate;
With endDates
As (
Select EndDate = eomonth(dateadd(month, t.n, @Startdate))
From (Values (0), (1), (2)) As t(n)
)
Select *
From endDates ed
Inner Join dbo.MyTable mt On mt.START_DATETIME >= ed.EndDate
And mt.START_DATETIME < dateadd(day, 1, ed.EndDate)
Now - if you want a fixed start date of 2019-01-01 through any given end date, up to a full year...
Declare @Startdate datetime = '2019-09-01'
, @endDate datetime = '2019-12-19';
Select @Startdate, @EndDate;
With endDates
As (
Select EndDate = eomonth(dateadd(month, t.n, @Startdate))
From (Values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) As t(n)
Where t.n <= datediff(month, @Startdate, @endDate)
)
Select *
From endDates ed
Inner Join dbo.MyTable mt On mt.START_DATETIME >= ed.EndDate
And mt.START_DATETIME < dateadd(day, 1, ed.EndDate)
I am sorry I am using Sqlserver 2008 R2 eomonth is not compatible.
E.g I am passing date parameter @Startdate datetime = '2019-09-01' it reach end of the month like startdate datetime = '2019-09-01' get the data of '2019-09-30' last date of the month . Results like below.
Date
2019-09-30 retrieve last day value
2019-10-31' retrieve last day value
2019-11-30' retrieve last day value
2019-12-31' retrieve last day value
The real problem is the -1 in the
SET @EndDate = dateadd(month,1+datediff(month,0,'11-NOV-19'),-1)
statement. That -1 garbage "short-cut" (kludge!) only works accurately if the previous month happens to have 31 days.
Instead, stick to the standard best-practice method for adjusting months, always using 0 at the end of the monthly dateadd calc. If you need to subtract (or add) a day(s), do it separately after the month calc.
For your current situation, you have an even easier and better method of calculating the end date, like so. Note that the final comparison is "<" the datetime, not <=.
DECLARE @StartDate DATETIME
DECLARE @NumberOfMonths int
DECLARE @EndDate DATETIME
SET @Startdate = '20190901'
SET @NumberOfMonths = 3
SET @StartDate = DATEADD(MONTH,DATEDIFF(MONTH,0, @StartDate),0) --- Run on any date will GO First date of the month.
SET @EndDate = DATEADD(MONTH, @NumberOfMonths, @StartDate) --- Run on any date will GO First date of the *next* month.
SELECT @Startdate 'startdate',
@EndDate 'enddate'
Your posts are anything but clear and it has led to a fair bit of speculation on this post... at least for me. Your posts have even led people into thinking that you always want to return just 3 months of data and that's certainly not what you posted in your original post. You've not even told us what the datatype of the "Start_DateTime" is and, yes, that does make a difference on how the last day of a month will be "grouped".
From your original post, it would appear that you could use any date/time (not just the start or end of the month) to identify the range of months you want data from and, even though you've not actually used a GROUP BY anywhere, it appears that you want one of two things...
Either the total just for the last day of each and every month in the date range
...or...
You want the total for each and every month but you want to return the totals with the date of the last day of each month as a bit of a "label" in the output.
Which is it or is it something else?
Whatever you do, we can't rely on what you're trying to do based on your code examples because, obviously, they're wrong or you wouldn't be asking for help.
You need to provide better information to help us help you better.
I've seen no solid evidence that always using 0 and never using -1 is an actual "Best Practice". I have seen some opinions as to why some folks dislike it but I wouldn't call it a "Best Practice" to avoid -1 in those places where it works well.
Also, calling it a "garbage "short-cut" (kludge!)" infers that those that do use it appropriately have written "garbage "short-cut" (kludge!)" code when the exact opposite may actually be true.
I'm also not sure where you came up with the idea that the -1 thing only works with months that happen to have 31 days. Your statement is simply untrue. Here's the code that proves it.
WITH cteGenDates AS
(--==== Generate the 15th of each month in 2019 (can be ANY day of the month)
SELECT SomeDate = CONVERT(DATE,DATEADD(mm,t.N,'2019-01-15'))
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))t(N)
)--==== Calculate the columns stated using the -1 correctly and effectively.
SELECT PrevMonEnd = CONVERT(DATE,DATEADD(mm,DATEDIFF(mm, 0,SomeDate),-1))
,CurrMonStart = CONVERT(DATE,DATEADD(mm,DATEDIFF(mm, 0,SomeDate), 0))
,SomeDate
,CurrMonEnd = CONVERT(DATE,DATEADD(mm,DATEDIFF(mm,-1,SomeDate),-1))
,NextMonStart = CONVERT(DATE,DATEADD(mm,DATEDIFF(mm,-1,SomeDate), 0))
FROM cteGenDates
;
Here are the results from that code. As you can see, the results came out just fine and correctly despite the use of -1 so, again... not sure where you came up with such an idea as the -1 thing working only for 31 day months.
Using @dateOffset we can move in either direction to get the start/end of months for any range of dates.
If I need a DATE data type returned - then EOMONTH does that and is easier to work with...even for generating the beginning of the month...and avoids an additional CAST/CONVERT.
The -1 is a hack, and it's not a worthwhile one. When adding dates, you should add top down, i.e., years first, then months, and last days. Besides, the best practice pattern for adjusting datetime value is:
[Do a Google search for "sql server dateadd(month, datediff(Month, 0, getdate()), 0)" if you want to see how often this method is recommended by vast numbers of different sites. Easily enough, in my view, to call it a best practice.]
This standard format works for HOUR, DAY, MONTH or YEAR. By using the -1 hack, you disrupt the pattern just for month. It's much clearer to simply subtract a day after the calc is that is what you need to do.
Besides, the -1 works only because that makes the date fall into December, which has 31 days. If -1 is good, then -31 should be good too, right? But it isn't. There were other examples of the errors that can occur from -1 on the discussion of this on the SQL Server Central site. The code gets copied and re-used without full understanding of it.
Everyone has their choice. I say stick to the standard patterns unless there's an extremely compelling reason not to. And a somewhat confusing hack to avoid a simple day calc afterward doesn't qualify for me.
I'll have to respectfully disagree, Scott. The -1 thing IS a standard thing that many of us understand and use. And you keep talking about the supposed "Best Practice" pattern of using only "0" but I know a lot of people that will say even that's not a "Best Practice" because it requires some advanced knowledge of date/time serial numbers and others that will say the use of -1 is definitely a "Best Practice".
And, if someone knows the -1 thing, it's perfectly clear. If they don't, then I'm not sure I want them working on the code to begin with.
As you say, "Everyone has their choice" but stop calling it a "garbage "short-cut" (kludge!)" because what you're calling a "kludge" is another person's "Best Practice" (although use of that term in this case is patently wrong in all the cases cited so far).
As for the "0" "Best Practice", there would be many that would chastise us both for using "0" instead of '1900-01-01' because it "adds more clarity". Still others would denounce the "hack" of using the DATEADD(DATEDIF()) combo and either insist on the use of a Calendar table or some other "hack" such as subtracting the DAY-1 from a whole (no time element) DATE to get the first of the month.
And, no... I don't call any of that a "garbage "short-cut" (kludge!)" because "It Depends".
And, you were still wrong about the -1 thing not working for any months except those that are 31 days long. The code proves it.
The -1 happens to go back to December, which has 31 days, which is why the hack works. It gets copied and causes issues all the time.
To me, it's the dozens of experts and expert sites that recommend the method, along with its inherent efficiency and accuracy, that do make it a best practice. It is the standard method to efficiently "round off" a datetime to a desired interval. The -1 is a kludge / non-standard add on, since it is only used with month. It looks "slick" because it "saves a step". I believe it's not worth it, since it does date calcs out of order.
Sir JeffModen first of all I am extremely sorry to make people thinking and speculation !
My requirement example:
I have a history years of data on daily basis and want to query data every last date of month , not requires any sum of the data only to get every last row data of the months.
Date
2019-01-31 00:00:00.000
2019-02-28 00:00:00.000
2019-03-31 00:00:00.000
2019-04-30 00:00:00.000
2019-05-31 00:00:00.000
So on
So Jeff, what then do you say is the actual best practice method of adjusting a datetime value to a specific interval? Since you claim the well-accepted method stated above is "not best practice", then what is? What, then, should we all be using instead?
If there actually is a better method, I'd love to hear about it.
Understood and appreciated. I also don't care for spelling out "month" or using lower case for SQL Server reserved words nor ever using Camel Casing in SQL Server. Because of the >= / < (closed/open) convention for criteria to identify date ranges, I'll also tell you that I'll never use EOMONTH.
BUT!!!!... I'll never call any of that a "garbage "short-cut" (kludge!)" because 1) that would be just a personal opinion with no real supportable stance because it IS just an opinion and 2) it might actually offend people that have found good utility in any of that, especially if they have a case sensitive server or they're simply used to doing it that way when writing non-SQL code.
As for the additional CAST/CONVERT, I only included that in my code to make the output pretty. In real life, there would be no reason to do so because '2019-01-01' is temporally identical to '2019-01-01 00:00:00.000'. You'll notice that I did the same thing in the creation of the SomeDate values. It was only for display purposes.
BTW, you used a -1 "seed" date in the DATEADD of your code and so I don't know what your objection to using the -1 thing actually is...
As for the EOMONTH() thing compared to doing a CONVERT() to get a DATE datatype out, Simplicity of code is only one thing to be considered. I also consider performance. I'm not working on a "quiet" machine right now and so that will have to wait until I get back home.
I also appreciate and understand the @dateOffset functionality you've built in but I wouldn't do that as a matter of rote in everything I do because it's an extra calculation and an extra variable. There are very few cases where I'd actually need to do such a thing in the type of work I do and just can't see burning the extra clock cycles on the millions of rows that I frequently work with.
To answer your second question first, I've already demonstrated such a method although I'm not claiming that it's better for everyone. It's better for me and the people I work with. It's ok for you to not like it.
As for your first question for a "Best Practice" on this, I'm not claiming there is one. I will admit that using only "0" is a "common practice" and that use of "-1" is a "less common practice" but that doesn't make "0" a "Best Practice" any more than "-1" is a "garbage "short-cut" (kludge!)" practice.
The "Best Practice" on this is "whatever someone is comfortable using with some exceptions". Obviously, I'm comfortable using "-1" for this in all aspects. Obviously, you're not and both are OK.
People "thinking and speculation" is good! I'm just trying to get to the proper definition of the problem you're trying to solve.
I mostly understand what you're trying to achieve but getting "every last row data of the months" and then posting only the last dates of each month is a bit confusing.
Are you trying to return exactly what you posted as in just the last date of each month?
Or are you trying to return all of the rows that occurred on the last date of each month?
Or, despite the number of rows per day, are you trying to return just one row (the last one temporarily speaking) that occurred for that month or the same idea for the last day of every month even if there were no entries for the last day of a given month?
Again, I have to ask because you're not stated how many rows can occur on the "last day of each month". We need to know more about the source table and it's contents. If you were to post some simplified example data that represents what you actually have in your history table, it would help a lot.
Yes - I am not against using -1 here...I don't like using this:
dateadd(month, datediff(month, 0, getdate()), -1)
This can easily be confused and you can get incorrect results when you try something like:
dateadd(month, datediff(month, 0, getdate()), -2)
Which does not give you the end of the month 2 months ago - rather it will return 11/30/2019 which is the same result as using -1. In fact, this does not give us a previous end of the month - it will give us -2 days if the previous month happens to have 31 days.
Using @dateOffset for me is much clearer...we are adding/subtracting the same interval whether that is second/minute/hour/day/week/month/quarter/year. You can also replace the 'seed' date to any valid date and it will work across all intervals - which is important once you start looking at second/minute/hour intervals which can exceed the allowable range.
Generally - the @dateOffset would only be utilized to set the parameters or variables and not against a column. If I needed to calculate the end of the month for a given column - I would remove that portion or use EOMONTH (again - depends on whether I need a datetime or date data type).
My concern with implicit cast/converts is related more to the execution plan. In some cases - an implicit convert in the execution plan will cause a cardinality issue which could impact the performance of the query - or worse, it could cause an excessive memory grant. Using EOMONTH returns a DATE data type and if the column being referenced is also a DATE - you don't have an implicit conversion and will avoid any cardinality issues.
All of this is just style issues - and is a completely separate argument...none of which is relevant to this conversation. I prefer spelling out the date intervals as it makes the code (for me) easier to read...especially when looking at the different abbreviations (mm or m = month, mi or n = minute, ss or s = second, ns = nanosecond, etc...).
