I have a client requesting an automated daily report that includes several month to date metrics. Normally I would use code like this to find start and end of month values:
SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) AS 'First calendar date of current month'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) AS 'Last calendar date of current month'
which would produce a result set of:
First calendar date of current month
2023-09-01 00:00:00.000
Last calendar date of current month
2023-09-30 23:59:59.000
The problem I have is that this client does not use the standard calendar month. Their calendar starts on the 2nd to last business day of the prior month and ends on the 3rd to last business day of the current month. Using this month as an example, the date range would be '2023-08-30 00:00:00.000' - '2023-09-27 23:59:59.000'. Everything I have read on the topic talks about counting business days in a month with DATEPART, when I need actual datetime values. Does anyone know if there is a way to achieve this?
1条答案
按热度按时间5rgfhyps1#
As the others have commented: It's recommended to create a calendar table. At minimum, you will need to know your Holidays and/or closure dates. I would turn this into two separate functions but below I'll show you as a single query.
Also, consider evaluating if there are slight changes for January. Example - If you run this on 1-15-2023, will they want the start date to run into the prior year?
DATEPART()
function, usingDW
as your datepart, will give you an integer for the week; 1 = Sunday 7 = Saturday.Example: