If there is no data in current month get the last month data

Question

I am new to SQL Server. I have a query which always return data in the current month. The problem is that just to the first day of every month, the query must give me the data of last month.

So, if I run the query on September 3, the query has to return the data from Sept 1 to Sept 3. However, if I run the query on Sept 1, it has to return all the data from August or if I run on October 1, all the data from September, etc..

I am trying to solve this using WHERE, BETWEEN and all of that.

Any ideas?

You should understand I am new in all of this and also English is not my native language, so it is complicated to my asking or explaining things. I get it, a lot of people just come here asking questions without any previous research or effort and that is not the idea of this website and it could be annoying. I did some previous research but the tools I was trying to use were so wrong... I will try to do my best in the next question or definitely I won't try to question again considering your comment. — Nelson FIGUEROA SERRANO, Sep 01 '21 at 22:21

score 2 · Accepted Answer · answered Sep 01 '21 at 19:27

2

You can try this.

DECLARE @StartDate DATE 
DECLARE @EndDate DATE

SET @StartDate = DATEADD(MONTH, CASE WHEN DAY(GETDATE()) = 1 THEN -1 ELSE 0 END ,  DATEADD(DAY, - (DAY(GETDATE()) - 1), GETDATE()))
SET @EndDate = CASE WHEN DAY(GETDATE()) = 1 THEN DATEADD(MONTH,1, @StartDate) ELSE GETDATE() END  


SELECT * FROM TheTable WHERE Date BETWEEN @StartDate AND @EndDate

answered Sep 01 '21 at 19:27

Serkan Arslan

13,158
4
29
44

Not safe if the date in the table is stored as a `datetime`, though. – allmhuran Sep 01 '21 at 19:56

allmhuran · Answer 2 · 2021-09-01T19:57:20.677

Think about the logic "imperatively" first.

if todays date is the first of the month then
   the query range is from the first of last month until the the end of last month
else
   the query range is from the start of the current month until today

Now start turning it into TSQL... (this will work regardless of whether your table stores the dates as date or datetime).

-- today
declare @today as date = getdate() -- implicit cast to date will remove time component

--if today's date is the first of the month
if (datepart(day, @today) = 1) 

-- from the first of last month:
>= dateadd(month, -1, @today)

-- until the the end of last month
< @today

-- else from the start of the current month...
>= datefromparts(year(@today), month(@today), 1)

-- until the end of today, which is the same as "before tomorrow"
< dateadd(day, 1, @today)

Put it all together!

declare @today date = getdate(); 

declare @startDate date = iif(
   datepart(day, @today) = 1, 
   dateadd(month, -1, @today), 
   datefromparts(year(@today), month(@today), 1)
);

declare @endDate date = iif(
   datepart(day, @today) = 1, 
   @today, 
   dateadd(day, 1, @today)
);

select ... where theDate >= @startDate and theDate < @endDate;

Wow, this is one of the best explanation methods I've ever seen. I should try to explain it like this too — Isi, Sep 01 '21 at 19:57

If there is no data in current month get the last month data

2 Answers2