如何避免SQL中的日期过滤问题？ [英] How to avoid date filtering issue in SQL?

问题描述

我根据搜索条件搜索少量记录。我前一天收到数据。 （如果我把数据拿到第8位，就会显示第7位的记录）。过滤日期字段都是日期时间数据类型，正在转换为日期。它在一台服务器上工作，采用相同的日期进行过滤，但这里采用的是上一个日期。两台服务器的存储过程相同。



我尝试过：



样本SP相同

I am searching few records based on the search criteria. I am getting the data one day before. (say if I take data for 8th , records of 7th is being shown). Filtering date fields all are in datetime data type which is being converted to date. It worked in one server taking the same date set for filtering but here taken previous date. Stored procedure are same in both servers.

What I have tried:

Sample SP for the same

-- EXEC [TestReport]  '2019-04-08', '2019-04-08'   

ALTER PROCEDURE [dbo].[TestReport]

@FromDate datetime,
@ToDate datetime 

AS
BEGIN

select * from test 
where
 
CONVERT(date,settlementDate)
between
 CONVERT(date,@FromDate) and  CONVERT(date,@ToDate)

 END

推荐答案

如果你从不同的日期获取数据到在您的查询中，我建议您的问题与时区有关。某处某个时区转换发生在您提供的日期，将其移回一天

If you're getting data from a different date to the one in your query, I'd suggest your issue has to do with time zones. Somewhere a time zone conversion is happening of the date you provide, moving it back a day

这篇关于如何避免SQL中的日期过滤问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！