Question Regarding Date logic
-
Hi,
I need to get Year, monthname in three letters, startdate and enddate of the month for last two years based on today’s date. Any sample query please
- KGJ-Dev - Friday, January 19, 2018 9:16 AM
You should find it easy enough from here:
SELECT bom = DATEADD(day,1,DATEADD(month,-1,x.eom)), x.eom
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)) d (n)
CROSS APPLY (SELECT eom = DATEADD(MONTH,-n,EOMONTH(GETDATE()))) x
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - KGJ-Dev - Friday, January 19, 2018 9:16 AM
You need to tell us how you intend to use the result. It WILL make a difference in what the EndDate should actually be (End of Month may NOT be the right value for this). I know you may not understand that right now so tell us how you're going to use the result so we can address that for you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Chris and Jeff thank you for the replies. i will hold these values in temp table and will be using it for further use in joining with other tables. those first and last date of month will be required for other operations.
Hi Chris, i got the below error. I am usingg sqlserver 2008R2
Msg 195, Level 15, State 10, Line 7
'EOMONTH' is not a recognized built-in function name - KGJ-Dev - Friday, January 19, 2018 11:23 AM
EOMONTH was added in SQL 2012. Chris' formula will also produce incorrect results whenever the current month does NOT have 31 days. Try making the following modification to his code.
SELECT bom = DATEADD(day,1,DATEADD(month,-1,x.eom)), x.eomFROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)) d (n)
CROSS APPLY (SELECT eom = DATEADD(MONTH,DATEDIFF(MONTH, -1, GETDATE()) - n, -1)) x
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Hi Drew,
Thank you and it's working. is it possible to pass the 24 as value? this 24 may change and it might be 18 or some other number. i will get this number as parameter to my proc. is there any way to make this 24 as variable number. - KGJ-Dev - Friday, January 19, 2018 12:18 PM
What have you tried?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Friday, January 19, 2018 11:57 AM
When I run this I get 10 beginning of month dates that look wrong to me.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. -
Hi Drew,
below is my try
DECLARE @EndDate DATETIME
,@StartDate DATETIME
,@Duration TINYINT = 15;SET @EndDate = getdate();
SET @StartDate = DATEADD(MM, - @Duration, @EndDate)SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, @EndDate) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, @EndDate) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, @EndDate) - N + 1, 0) - 1 enddate
FROM (
VALUES (0)
,(1)
,(2)
,(3)
,(4)
,(5)
,(6)
,(7)
,(8)
,(9)
,(10)
,(11)
,(12)
,(13)
,(14)
,(15)
,(16)
,(17)
,(18)
,(19)
,(20)
,(21)
,(22)
,(23)
,(24)
) x(N)
WHERE N <= DATEDIFF(MONTH, @StartDate, @EndDate);I am not sure how do i apply this on your query. because you have used cross apply to calculate EOM. Curious to know about how can i achieve the same in your query
-
All you have to set / pass in is the "@number_of_months", although you could pass in a different ending month if you wanted to.
DECLARE @number_of_months int
SET @number_of_months = 24--------------------------------------------------------------------------------
DECLARE @start_month date
DECLARE @end_month date--SET @end_month = '20171113'
SET @end_month = ISNULL(@end_month, GETDATE())
SET @start_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, @end_month) - @number_of_months + 1, 0);WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
SELECT YEAR(first_day_of_month) AS year, CAST(DATENAME(MONTH, first_day_of_month) AS char(3)) AS mon,
first_day_of_month, DATEADD(DAY, -1, DATEADD(MONTH, 1, first_day_of_month)) AS last_day_of_month
FROM cteTally100 months
CROSS APPLY (
SELECT DATEADD(MONTH, months.number - 1, @start_month) AS first_day_of_month
) AS ca1
WHERE months.number <= @number_of_monthsSQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Thank you scott for sharing another way of doing this.
- KGJ-Dev - Friday, January 19, 2018 11:23 AM
"Required for other operations" doesn't tell me much. What kind of operations? Are you talking about using the start and end dates as join criteria to group dated rows? If so, EOMONTH is the wrong way to go. You should also bullet proof your code by anticipating someone using using times in the date columns of those "other tables in those other operations" as well. So, what are the "other operations" that you're talking about. Be specific about the method(s) that will be using the start and end dates.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - drew.allen - Friday, January 19, 2018 11:57 AMCheers Drew - but there's an error in your version too:
DECLARE @getdate-2 DATETIME = '20170228'-- Drew's code
SELECT bom = DATEADD(day,1,DATEADD(month,-1,x.eom)), x.eom
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)) d (n)
CROSS APPLY (SELECT eom = DATEADD(MONTH,DATEDIFF(MONTH, -1, @getdate-2) - n, -1)) x-- Chris v2
SELECT bom, eom = DATEADD(DAY,-1,DATEADD(MONTH,1,bom))
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)) d (n)
CROSS APPLY (SELECT bom = DATEADD(MONTH,DATEDIFF(MONTH,0,@GetDate) - n,0)) x“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
This may be 'the long way around the barn', but it works. You could set this up a a SP and pass it the number of months you want and a start date(current date).
DECLARE @months AS INT;
DECLARE @startdate AS DATE;
DECLARE @Begindate AS DATE;DECLARE @datetable TABLE
(
Year_int INT,
Month_Int INT,
Month_Abv CHAR(3),
BOM_Date DATE,
EOM_Date DATE
)
;SET @months = 30;
SET @startdate = GETDATE();SET @Begindate = (SELECT DATEADD(dd, -(DATEPART(dd, @startdate) - 1), @startdate));
LOOPHERE:
INSERT INTO @datetable
SELECT YEAR(@Begindate) AS Year_int,
MONTH(@Begindate) AS Month_Int,
LEFT(CONVERT(CHAR(11), @Begindate, 107), 3) AS Month_Abv,
@Begindate AS BOM_Date,
DATEADD(dd, -1, DATEADD(MM, 1, @Begindate)) AS EOM_Date
;SET @months = @months -1;
SET @Begindate = (SELECT DATEADD(mm, -1, @Begindate));
IF @months > 0
GOTO LOOPHERE;
SELECT *
FROM @datetable
;-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. - below86 - Monday, January 22, 2018 9:48 AM
A loop is horribly inefficient compared to the tally table method outlined earlier in this thread.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 104 total)
You must be logged in to reply to this topic. Login to reply