在where子句的日期范围过滤器中使用datetimeoffset日期
问题描述:
使用SQL Server 2016.它位于报表的SP中。当给定2017年2月22日的日期范围时,报告包括2017年2月21日的项目。日期作为DateTimeOffset存储在数据库中。在where子句的日期范围过滤器中使用datetimeoffset日期
在这个查询我amtrying回到22日,但我也得到第21。
@start和@end代表用户输入的日期范围。 @storeddate是用于过滤报表的数据库的日期。我的计划是转换偏移日期,然后拉出'日期'部分过滤,但它不工作。
declare @start date = '2017-02-22';
declare @end date = '2017-02-22';
declare @storeddate datetimeoffset = '2017-02-22 00:00:19.0000000 +00:00';
;with dates as
(
select @storeddate as 'raw'
, @storeddate AT TIME ZONE 'Pacific Standard Time' as offset
, CONVERT(datetime, @storeddate) AT TIME ZONE 'Pacific Standard Time' as dt
, CONVERT(datetime, @storeddate AT TIME ZONE 'Pacific Standard Time') as d
, CAST(CONVERT(datetime, @storeddate) AT TIME ZONE 'Pacific Standard Time' as date) as 'casted'
, @start as 'start'
, @end as 'end'
)
select * from dates
WHERE (
CAST(CONVERT(datetime, @storeddate) AT TIME ZONE 'Pacific Standard Time' as date) >= @start
AND CAST(CONVERT(datetime, @storeddate) AT TIME ZONE 'Pacific Standard Time' as date) < DATEADD(day, 1, @end))
编辑加注:
同类奇怪的场景。这是一个仅在俄勒冈州使用的Intranet网络应用程序。 Web开发人员使用了一些JavaScript datepicker库,将他的所有日期都改为UTC,并且他无法弄清楚如何将其更改回来,以便将它们作为datetimeoffset存储在数据库中。所以现在我必须更改所有报告以显示正确的日期。 '在时区'固定显示报告中的日期,但它不适用于日期范围过滤器的where子句。
答
我对放错了地方的CONVERT右括号,所以我的@storeddate转换为日期,而不是@storedate在时间区。因此,答案是这样的:
WHERE CAST(CONVERT(datetime, @storeddate AT TIME ZONE 'Pacific Standard Time') as date)
,而不是
WHERE CAST(CONVERT(datetime, @storeddate) AT TIME ZONE 'Pacific Standard Time' as date)
等等
答
这是否与您的时区有关?您将@storeddate放入没有偏移量的位置,然后看起来您正在评估太平洋时间(-8:00,是不是?)。看起来这样会改变返回的数据。
编辑:尝试使用DATEADD修改日期:
declare @start date = '2017-02-22';
declare @end date = '2017-02-22';
declare @storeddate table (rawdate datetimeoffset)
insert into @storeddate
values('2017-02-22 00:00:19.0000000 +00:00')
,('2017-02-21 00:00:19.0000000 +00:00')
,('2017-02-22 00:18:19.0000000 +00:00')
,('2017-02-23 00:18:19.0000000 +00:00')
;with dates as
(
select rawdate as 'raw'
, cast(dateadd(hh,-8,rawdate) as date) as offset
, @start as 'start'
, @end as 'end'
from @storeddate
)
select * from dates
WHERE (
offset >= @start
and offset < DATEADD(day, 1, @end))
同类奇怪的场景。这是一个仅在俄勒冈州使用的Intranet网络应用程序。 Web开发人员使用了一些JavaScript datepicker库,将他的所有日期都改为UTC,并且他无法弄清楚如何将其更改回来,以便将它们作为datetimeoffset存储在数据库中。所以现在我必须更改所有报告以显示正确的日期。 '在时区'固定显示报告中的日期,但它不适用于日期范围过滤器的where子句。 – BattlFrog
尝试使用dateadd函数来确定偏移量,而不是依赖“At Time Zone”。我认为这只影响显示,而不是日期的存储方式。我试图粘贴一个例子,并有格式化问题。 –