April 22, 2022 at 6:15 pm
Hello,
I'm trying to get the first and last dates of all the months based on the current year.
1/1/2022
1/31/2022
2/1/2022
2/28/2022
3/1/2022
3/31/2022
Any help would be greatly appreciated. Thank you in advance!
April 22, 2022 at 6:34 pm
Given a tally/number table or function (e.g., https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally):
DECLARE @year INT = DATEPART(YEAR,GETDATE());
SELECT
DATEFROMPARTS(@year,Numbers.Number,1) AS FirstOfMonth,
DATEADD(month, DATEDIFF(month, 0, DATEFROMPARTS(@year,Numbers.Number,1)) + 1, -1) AS LastOfMonth
FROM util.Numbers
WHERE Numbers.Number >=1
AND Number <= 12;
April 22, 2022 at 7:23 pm
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
)
SELECT
DATEADD(MONTH, t.number, start_of_year) AS start_of_month,
DATEADD(DAY, -1, DATEADD(MONTH, t.number + 1, start_of_year)) AS end_of_month
FROM cte_tally100 t
CROSS APPLY ( SELECT CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS date) AS start_of_year ) AS calc1
WHERE t.number BETWEEN 0 AND 11
ORDER BY t.number
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 22, 2022 at 7:34 pm
Rather than adding a month and taking away a day to get the end of month (which is how I have always done it) there is a function called EOMONTH which does it for you. I don't know if the traditional method is more or less efficient, I was just a bit gobsmacked when I found out it already existed.
EOMONTH(DATEADD(MONTH, t.number, start_of_year)) AS end_of_month
April 22, 2022 at 8:03 pm
Rather than adding a month and taking away a day to get the end of month (which is how I have always done it) there is a function called EOMONTH which does it for you. I don't know if the traditional method is more or less efficient, I was just a bit gobsmacked when I found out it already existed.
EOMONTH(DATEADD(MONTH, t.number, start_of_year)) AS end_of_month
True, if the OP is on SQL 2012 or higher. I don't like to assume such things (yes, I know, this is 2016 forum, but 2008- forums are so dead that, if I were still on 2008, I would post here anyway).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 22, 2022 at 9:12 pm
There isn't a need to build a tally table or calculate the month numbers. That can be done simply using VALUES to return 0 through 11 or 1 through 12 (depending on which one you decide to use):
Select *
, fom_datetime = dateadd(month, dt.mth, y.foy)
, eom_datetime = dateadd(day, -1, dateadd(month, dt.mth + 1, y.foy))
From (Values (datetimefromparts(year(getdate()), 1, 1, 0, 0, 0, 0))) As y(foy)
Cross Apply (Values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) As dt(mth)
But - it does depend on what data type you are returning. If you only need a date data type - then you can do the following:
Select *
, fom_date = datefromparts(year(getdate()), dt.mth, 1)
, eom_date = eomonth(datefromparts(year(getdate()), dt.mth, 1))
From (Values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) As dt(mth)
You could also get the date/time returns using something like this:
Select *
, fom_datetime = datetimefromparts(year(getdate()), dt.mth, 1, 0, 0, 0, 0)
, eom_datetime = cast(eomonth(datefromparts(year(getdate()), dt.mth, 1)) As datetime)
From (Values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) As dt(mth)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 22, 2022 at 9:23 pm
And here is one more method - using the second parameter of EOMONTH:
Select *
, fom_date = datefromparts(year(getdate()), dt.mth + 1, 1)
, eom_date = eomonth(datefromparts(year(getdate()), 1, 1), dt.mth)
From (Values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) As dt(mth)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 22, 2022 at 11:51 pm
Hello,
I'm trying to get the first and last dates of all the months based on the current year.
1/1/2022
1/31/2022
2/1/2022
2/28/2022
3/1/2022
3/31/2022
Any help would be greatly appreciated. Thank you in advance!
You've got multiple answers on how to do this Now, let's get to the heart of the matter because you may actually be asking the wrong question.
What do you intend to use this output for?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2022 at 1:34 pm
This was removed by the editor as SPAM
May 31, 2022 at 2:16 pm
A bit of a sidebar... I almost always avoid both EOMonth and anything else that is used to calculate the last day of the month because so many people forget that if the have code that that is trying to get all of the data for a given month, they write it in such a fashion that the all but the first instant of the last day is NOT included. It also makes it so that if someone that has a DATE-only column and they decided to change it to include a time (regardless of scale), you now how to remember to go and look everywhere for code that uses the column and fix the code. In other words, EOMonth and similar methods are prone to making your code non-bullet proof.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2022 at 2:19 pm
Thanks for the answer.
Hi and welcome aboard, Robert.
Understand that your reply looks like a "prelude to spam" that spammers regularly use to see if they successfully got a working account. They also later come back a day or two later and add some spam. If you're not a spammer then, like I said, welcome aboard. If you are a spammer, your posts won't last long on this site and you should simply go away. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2022 at 3:52 pm
A bit of a sidebar... I almost always avoid both EOMonth and anything else that is used to calculate the last day of the month because so many people forget that if the have code that that is trying to get all of the data for a given month, they write it in such a fashion that the all but the first instant of the last day is NOT included. It also makes it so that if someone that has a DATE-only column and they decided to change it to include a time (regardless of scale), you now how to remember to go and look everywhere for code that uses the column and fix the code. In other words, EOMonth and similar methods are prone to making your code non-bullet proof.
This gets mentioned and then it's not a full explanation imo. To be clear the issue you're framing could be demonstrated by the following where 3 rows are selected instead of 4?
drop table if exists #MailIncoming;
go
create table #MailIncoming(
MailIncoming DateTime,
MailAnswering DateTime);
insert into #MailIncoming values
('2021-04-01 16:30:00', '2021-04-02 14:00:00'),
('2021-04-01 09:30:00', '2021-04-03 14:00:00'),
('2021-04-03 12:30:00', '2021-04-05 10:00:00'),
('2021-04-04 11:00:00', '2021-04-30 18:00:00');
select *
from #MailIncoming
where MailAnswering>='2021-04-01'
and MailAnswering<=EOMONTH(MailAnswering);
If the column had been altered from DATE to DATETIME it surely could be problematical. How likely of a scenario idk. Where EOMONTH is convenient is with relative date queries
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 31, 2022 at 7:53 pm
Jeff Moden wrote:A bit of a sidebar... I almost always avoid both EOMonth and anything else that is used to calculate the last day of the month because so many people forget that if the have code that that is trying to get all of the data for a given month, they write it in such a fashion that the all but the first instant of the last day is NOT included. It also makes it so that if someone that has a DATE-only column and they decided to change it to include a time (regardless of scale), you now how to remember to go and look everywhere for code that uses the column and fix the code. In other words, EOMonth and similar methods are prone to making your code non-bullet proof.
This gets mentioned and then it's not a full explanation imo. To be clear the issue you're framing could be demonstrated by the following where 3 rows are selected instead of 4?
drop table if exists #MailIncoming;
go
create table #MailIncoming(
MailIncoming DateTime,
MailAnswering DateTime);
insert into #MailIncoming values
('2021-04-01 16:30:00', '2021-04-02 14:00:00'),
('2021-04-01 09:30:00', '2021-04-03 14:00:00'),
('2021-04-03 12:30:00', '2021-04-05 10:00:00'),
('2021-04-04 11:00:00', '2021-04-30 18:00:00');
select *
from #MailIncoming
where MailAnswering>='2021-04-01'
and MailAnswering<=EOMONTH(MailAnswering);If the column had been altered from DATE to DATETIME it surely could be problematical. How likely of a scenario idk. Where EOMONTH is convenient is with relative date queries
Heh... it was a full explanation... just no demo. Didn't believe anyone would need one. Thanks for putting one together.
As for how likely the DATE to DATETIME column changes is, it would only take once for you to realize that it's better to be safe than sorry and understand that it's actually easier to do because all you have to do is find the first of each month rather than the first and last of each month. 😀 And, yeah... it's happened at least once at every company I've worked at in the last 25 years.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply