CODEWITHSUNDEEP
sqlsql-serverreporting-services
Triston77
Triston77

Reputation: 1

Needing to get first of month in SQL Server, but from yesterday's date

I am working on a report that will send daily with the full months data minus the current day as it sends early and won't have relevant data from today.

Currently the parameter for the start as =dateadd(“m”,1,dateserial(year(Today),month(Today),0))

and the end as =DateAdd("d",-1,Today())

The problem is that this will skip the last day of each month. I want to change the start date to the first of the month from today - 1 day, but I am not sure how to do this?

I could change it to send with the current days data as well but with it sending at 8 AM the data would still be missing.

Upvotes: 0

Views: 383

Answers (1)

Stuck at 1337
Stuck at 1337

Reputation: 2084

Probably least amount of code is to (1) subtract a day from today, (2) take the eomonth of the previous month, and (3) add a day.

SELECT DATEADD(DAY, 1, EOMONTH(DATEADD(DAY, -1, GETDATE()),-1));

I'm not sure what language you're doing dateserial stuff in but I'd personally do this inside SQL Server.

Also the best way to drive a report for any period is to say:

col >= start of the period
AND
col <  start of *next* period

For example, for a report to cover all of last month (I realize that's not quite what you're doing, it's just a simpler example), I would say:

DECLARE @start date = DATEADD(DAY, 1, EOMONTH(GETDATE(), -2));

...
WHERE date_column >= @start
  AND date_column <  DATEADD(MONTH, 1, @start);

This way you don't get bitten by all of the problems that can happen with various date/time types as you try to figure out what is the end of a day or month.

For month-to-date covering yesterday and not today, I would say:

DECLARE @today date = GETDATE();
DECLARE @start date = DATEADD(DAY, 1, EOMONTH(DATEADD(DAY, -1, @today),-1));

... WHERE date_column >= @start
      AND date_column <  @today;

Upvotes: 2

Related Questions