This post has been wikified, any user with enough reputation can edit it.
asked
Query to select records for a previous month
I need to write a report that lists all employee expenses for the previous month. The report will be scheduled to run on the first of the month. I am using the following query. It works except expenses for December. When report will kick off on January 1, it will be blank. December expenses will be skipped SELECT employeeid ,employeeName , finalApproval FROM dbo.ExpenseExport WHERE DATEPART(month, expensedate) = (DATEPART(month, GETDATE()) - 1) DATEPART(year, expensedate l) = DATEPART(year, DATEADD(m, -1GETDATE())) What do I need to do to get December expnses comes January 1.
It looks like it would be safer to use a couple of variables in your report, one of them equal to the 1st of previous month and the other equal to the 3 milliseconds before the 1st of current month. This way you will capture all records which have expensedate any time last month and you will not depend on any datepart which leads to the problem when you run your report on January 1st because the year parts do not match. Here is the script: declare @startDate datetime; -- 1st day of previous month declare @endDate datetime; -- 3 milliseconds before current month starts select @startDate = dateadd(month, datediff(month, 0, getdate()) - 1, 0), @endDate = dateadd(millisecond, -3, dateadd(month, 1, @startdate)); -- your report query select employeeid, employeeName, finalApproval from dbo.ExpenseExport where expensedate between @startDate and @endDate; You can test this script for January 1st report by replacing the **getdate()** with the datetime value corresponding to any day in January of this year, such as January 1st. If you replace the desired datetime value using single quotes then 'YYYYMMDD' format is the best one to use because it does not depend on your servers dateformat settings. So for January 1st of this year you could use '20120101'. Here is the test showing that the values are correct: declare @startDate datetime; -- 1st day of previous month declare @endDate datetime; -- 3 milliseconds before current month starts select @startDate = dateadd(month, datediff(month, 0, '20120101') - 1, 0), @endDate = dateadd(millisecond, -3, dateadd(month, 1, @startdate)); select @startDate StartDate, @endDate EndDate; -- the above script returns: StartDate EndDate ----------------------- ----------------------- 2011-12-01 00:00:00.000 2011-12-31 23:59:59.997 Oleg
It may also help to use a calculation that gets a full date based on the current date.
SELECT CONVERT(DATETIME, DATEDIFF(d, 0, DATEADD(dd, - (DAY(DATEADD(mm, 1, GETDATE()))-1), DATEADD(mm, -1, GETDATE())))) LastFirst,
CONVERT(DATETIME, DATEDIFF(d, 0, DATEADD(dd, - (DAY(DATEADD(mm, 1, GETDATE()))-1), DATEADD(mm, 0, GETDATE())))) ThisFirst
This will give you the first of last month and the first of this month.
4 People are following this question.
