查询两个日期之间的记录，其中日期可能是 1900-01-01

发布于 2025-01-17 02:25:12 字数 1074 浏览 1 评论 0原文

想象一下这个。您需要查询定价数据表，以了解给定特定日期的部件号的当前定价。您的表格格式如下：如果“起始日期”为 '1900-01-01 00:00:00.000' 并且“截止日期”为 '2021-06-07 00： 00:00.000'，则表示“截止日期”之前的任何日期均有效。

还有另一条记录的“从日期”2021-06-08 00:00:00.000 到 '1900-01-01 00:00:00.000'，这意味着日期之后的任何事情。

这是直观的表格

itemrelation	fromdate	todate	amount
A123456	1900-01-01 00:00:00.000	2021-06-07 00:00:00.000	1578.300000000000
A123456	2021-06-08 00:00:00.000	1900-01-01 00:00:00.000	1586.300000000000

假设用户想要检查今天的价格 2022-03-24

查询无法运行，

SELECT itemrelation, fromdate, todate, amount
  FROM [Ax2009Live].[dbo].[PRICEDISCTABLE] 
  where itemrelation = 'A123456'
  and fromDate <= '2022-03-24'
  and toDate >= '2022-03-24'

因为“1900”日期将阻止查询返回任何结果。

是否有某种类型的 CASE 语句可以解决这个问题？一些我不知道的内置 SQL 命令？

感谢您的帮助。

原文

Imagine this. You need to query your pricing data table to find out what the current pricing is on a part number given a specific date. You table is formatted so that if the 'from date' is '1900-01-01 00:00:00.000' and your 'to date' is '2021-06-07 00:00:00.000', then that means any date before the 'to date' is valid.

There is also another record with 'from date' 2021-06-08 00:00:00.000 to '1900-01-01 00:00:00.000', which means anything after the date.

Here is the table visually

itemrelation	fromdate	todate	amount
A123456	1900-01-01 00:00:00.000	2021-06-07 00:00:00.000	1578.300000000000
A123456	2021-06-08 00:00:00.000	1900-01-01 00:00:00.000	1586.300000000000

Lets say the user wants to check what the price is today 2022-03-24

The query ran cannot be

SELECT itemrelation, fromdate, todate, amount
  FROM [Ax2009Live].[dbo].[PRICEDISCTABLE] 
  where itemrelation = 'A123456'
  and fromDate <= '2022-03-24'
  and toDate >= '2022-03-24'

because of the "1900" dates will prevent the query from returning any results.

Is there some type of CASE statement that would work to solve this problem? Some built in SQL command I don't know about?

Thanks for your help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

瞎闹 2025-01-24 02:25:12

谢谢@HoneyBadger，

这是根据您的建议进行的查询。效果很好。

DECLARE @ITEMRELATION AS NVARCHAR(50)
SET @ITEMRELATION = 'A123456'

DECLARE @DATE AS DATETIME
SET @DATE = '2022-03-24'

SELECT itemrelation, fromdate, todate, amount
  FROM [Ax2009Live].[dbo].[PRICEDISCTABLE] 
  WHERE 
  itemrelation = @ITEMRELATION
  and fromDate <= @DATE
  and toDate >= @DATE
  OR 
  itemrelation = @ITEMRELATION
  and fromDate <= @DATE
  and toDate = '1900-01-01'
  OR 
  itemrelation = @ITEMRELATION
  and fromDate = '1900-01-01'
  and toDate >= @DATE

Thank you @HoneyBadger,

Here is the query based on your suggestion. It works great.

DECLARE @ITEMRELATION AS NVARCHAR(50)
SET @ITEMRELATION = 'A123456'

DECLARE @DATE AS DATETIME
SET @DATE = '2022-03-24'

SELECT itemrelation, fromdate, todate, amount
  FROM [Ax2009Live].[dbo].[PRICEDISCTABLE] 
  WHERE 
  itemrelation = @ITEMRELATION
  and fromDate <= @DATE
  and toDate >= @DATE
  OR 
  itemrelation = @ITEMRELATION
  and fromDate <= @DATE
  and toDate = '1900-01-01'
  OR 
  itemrelation = @ITEMRELATION
  and fromDate = '1900-01-01'
  and toDate >= @DATE

回复收藏 0 原文

~没有更多了~