SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question Regarding Date logic


Question Regarding Date logic

Author
Message
KGJ-Dev
KGJ-Dev
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2457 Visits: 987
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
ChrisM@Work
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103609 Visits: 20843
KGJ-Dev - Friday, January 19, 2018 9:16 AM
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


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 Shaw

For 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
Exploring Recursive CTEs by Example Dwain Camps
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (542K reputation)SSC Guru (542K reputation)SSC Guru (542K reputation)SSC Guru (542K reputation)SSC Guru (542K reputation)SSC Guru (542K reputation)SSC Guru (542K reputation)SSC Guru (542K reputation)

Group: General Forum Members
Points: 542721 Visits: 44633
KGJ-Dev - Friday, January 19, 2018 9:16 AM
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


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.
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 problems
How to post performance problems
Forum FAQs
KGJ-Dev
KGJ-Dev
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2457 Visits: 987
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
drew.allen
drew.allen
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39691 Visits: 14389
KGJ-Dev - Friday, January 19, 2018 11:23 AM
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

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.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()) - n, -1)) x



Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
KGJ-Dev
KGJ-Dev
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2457 Visits: 987
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
drew.allen
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39691 Visits: 14389
KGJ-Dev - Friday, January 19, 2018 12:18 PM
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.

What have you tried?

Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
below86
below86
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4034 Visits: 3792
drew.allen - Friday, January 19, 2018 11:57 AM
KGJ-Dev - Friday, January 19, 2018 11:23 AM
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

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.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()) - n, -1)) x



Drew

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
KGJ-Dev
KGJ-Dev
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2457 Visits: 987
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

ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50064 Visits: 8240
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_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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search