如果当前月份没有数据，则获取上个月的数据

Question

我是SQL Server的新手。我有一个查询，总是返回当前月份的数据。问题是，直到每个月的第一天，查询必须给我上个月的数据。

所以，如果我在9月3日运行查询，查询必须返回从9月1日到9月3日的数据。但是，如果我在9月1日运行查询，它必须返回8月的所有数据，或者如果我在10月1日运行查询，则必须返回9月的所有数据，等等。

我正试图用WHERE,BETWEEN和所有这些来解决这个问题。

任何想法?

Answer 1

你可以试试这个

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

Answer 2

思考逻辑"命令式";第一。

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

现在开始把它变成TSQL…(无论您的表将日期存储为date还是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)

把它们放在一起!

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;