March 5, 2022 at 3:25 pm
Hi, I have a report that I want to configure that will be sending out a report monthly to users. The trouble I am having is trying to get the excel file to send data to those users on the first day of a month with data of the previous month. I am not sure what to put into the date format in the query of the report. Can anyone help?
March 5, 2022 at 3:36 pm
So you will want to do some date math.
You should treat all dates as the ISO standard and to avoid any ambiguity use YYYY-MM-DD formatting when passing dates to SQL.
So on the first of the month you would want to do something like the below where clause in your query.
WHERE datecolumn >= dateadd(month,-1,convert(date,getdate())) and datecolumn < convert(date,getdate())
Which would get you the data for this date range, assuming you ran this on 2022-03-01.
2022-02-01 00:00:00.000 to 2022-02-28 23:59:59.997
March 7, 2022 at 4:39 pm
WHERE datecolumn >= dateadd(month,-1,convert(date,getdate())) and datecolumn < convert(date,getdate())
Which would get you the data for this date range, assuming you ran this on 2022-03-01.
2022-02-01 00:00:00.000 to 2022-02-28 23:59:59.997
This code will only work on the first day of the month
This will work for any date in the next month for the preceding month
WHERE YourDateColumn >= DATEADD(day, 1, EOMONTH(getdate(), -2))
AND YourDateColumn < DATEADD(DAY, 1, EOMONTH(getdate(), -1))
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 7, 2022 at 7:44 pm
Another option
WHERE YourDateColumn >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) --First of previous month
AND YourDateColumn < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) --First of this month
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
March 8, 2022 at 3:05 am
So you will want to do some date math.
You should treat all dates as the ISO standard and to avoid any ambiguity use YYYY-MM-DD formatting when passing dates to SQL.
Ah, be careful now. In SQL Server, the hyphenated version of the "ISO Standard" as you have it is actually language sensitive. If the current language is, for example, FRENCH, then the hyphenated version comes out in the YYYY-DD-MM format. I'm not sure what Excel does if the language is different.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 8, 2022 at 3:13 am
Another option
WHERE YourDateColumn >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) --First of previous month
AND YourDateColumn < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) --First of this month
That's what I use because it has a very high amount of symmetry when using different datatypes . The other advantage is, although the code is slightly longer than the EOMonth() method, it's also a little over 20% faster, to boot.
And why MS didn't come out with an FOMonth() function at the same time they came out with EOMonth is just crazy to me especially you consider how many people make the mistake of using EOMonth() when times are present in the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply