Log in  ::  Register  ::  Not logged in

## Question Regarding Date logic

 Author Message KGJ-Dev SSCarpal Tunnel Group: General Forum Members Points: 4665 Visits: 1017 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 ChrisM@Work SSC Guru Group: General Forum Members Points: 166830 Visits: 21750 +xKGJ-Dev - Friday, January 19, 2018 9:16 AMHi, 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 pleaseYou should find it easy enough from here: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,-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 ModenExploring Recursive CTEs by Example Dwain Camps Jeff Moden SSC Guru Group: General Forum Members Points: 884717 Visits: 47951 +xKGJ-Dev - Friday, January 19, 2018 9:16 AMHi, 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 pleaseYou 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs KGJ-Dev SSCarpal Tunnel Group: General Forum Members Points: 4665 Visits: 1017 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 2008R2Msg 195, Level 15, State 10, Line 7'EOMONTH' is not a recognized built-in function name drew.allen SSC Guru Group: General Forum Members Points: 65834 Visits: 17453 +xKGJ-Dev - Friday, January 19, 2018 11:23 AMHi 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 2008R2Msg 195, Level 15, State 10, Line 7'EOMONTH' is not a recognized built-in function nameEOMONTH 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 AllenBusiness Intelligence AnalystPhiladelphia, PAHow to post data/code on a forum to get the best help.How to Post Performance ProblemsMake sure that you include code in the appropriate IFCode tags, e.g. [code=sql][/code]. You can find the IFCode tags under the INSERT options when you are writing a post. KGJ-Dev SSCarpal Tunnel Group: General Forum Members Points: 4665 Visits: 1017 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. drew.allen SSC Guru Group: General Forum Members Points: 65834 Visits: 17453 +xKGJ-Dev - Friday, January 19, 2018 12:18 PMHi 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. What have you tried?Drew J. Drew AllenBusiness Intelligence AnalystPhiladelphia, PAHow to post data/code on a forum to get the best help.How to Post Performance ProblemsMake sure that you include code in the appropriate IFCode tags, e.g. [code=sql][/code]. You can find the IFCode tags under the INSERT options when you are writing a post. below86 SSCrazy Eights Group: General Forum Members Points: 8976 Visits: 5674 +xdrew.allen - Friday, January 19, 2018 11:57 AM+xKGJ-Dev - Friday, January 19, 2018 11:23 AMHi 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 2008R2Msg 195, Level 15, State 10, Line 7'EOMONTH' is not a recognized built-in function nameEOMONTH 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`DrewWhen 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 KGJ-Dev SSCarpal Tunnel Group: General Forum Members Points: 4665 Visits: 1017 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 enddateFROM ( 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 ScottPletcher SSC Guru Group: General Forum Members Points: 85821 Visits: 9647 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 intSET @number_of_months = 24--------------------------------------------------------------------------------DECLARE @start_month dateDECLARE @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);WITHcteTally10 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_monthFROM cteTally100 monthsCROSS APPLY ( SELECT DATEADD(MONTH, months.number - 1, @start_month) AS first_day_of_month) AS ca1WHERE months.number <= @number_of_months` SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.