Query

無法在查詢中使用今天的日期作為條件

  • June 18, 2019

好的，自從我使用 Access 和 SQL 以來已經過去了十年，所以請原諒。

短版 - 當我添加查詢條件以僅顯示與今天日期匹配的行時，查詢不返回任何行；儘管那裡確實有匹配的行。

我有一個偷偷摸摸的懷疑

$$ FG Orders by Date $$.$$ FG Date $$不知何故被視為文本，因為在查看查詢的排序結果後，無論排序順序如何，它都沒有正確排序欄位。 希望以下資訊足以讓某人幫助我了解我哪裡出錯了。

正在解決的問題

查詢和來自它的報告是廚房生產報告，它顯示了需要為當天必鬚髮送的成品生產的組件貨物。

佈局精美的表格很複雜，可以很好地管理事物，這從下面的 SQL 中會變得很明顯。

由於工作流程的原因，此報告只需要顯示當天的資訊。

概括

我有一個查詢，日期列在哪裡

$$ FG Orders by Date $$.$$ FG Date $$，其格式為短日期 (DD/MM/YYYY)。 我已經通過創建一個 Expression 列將其轉換為不同的格式來測試它的格式是否正確，這可以完美地工作。

查詢返回的行沒有問題。

每當我向查詢添加條件以僅返回今天日期 (13/06/2019) 的行時 - 使用 Date() 時，儘管有 11 行具有該日期值，但查詢不會返回任何行。

再次刪除標準，一切都會很好地恢復。

SQL

這是未添加條件的查詢程式碼：

SELECT [FG Orders by Date].FGOrderID, 
      [FG Orders by Date].[FG Date], 
      [FG Orders by Date].[FG Date] AS ExpTodaysDate, 
      [FG Orders by Date].FGSKU, 
      [Finished Goods].[FG Name], 
      [FG to CG mapping].CGSKU, 
      [Component Goods].[CG Category], 
      [CG Category].[CG Category Name], 
      [Component Goods].[CG Name], 
      [Component Goods].[CG Unit], 
      Units.[Unit name], 
      [Component Goods].[CG Unit value], 
      [FG Orders by Date].[FGSKU Quantity], 
      [FG to CG mapping].[CG Qty Required for FGSKU], 
      [FGSKU Quantity]*[CG Qty Required for FGSKU] AS TotalQtyOfCGSKU
FROM (Units INNER JOIN ([CG Category] INNER JOIN [Component Goods] ON [CG Category].[CG Category ID] = [Component Goods].[CG Category]) ON Units.[Unit Symbol] = [Component Goods].[CG Unit]) INNER JOIN (([Finished Goods] INNER JOIN [FG Orders by Date] ON [Finished Goods].[FGSKU] = [FG Orders by Date].[FGSKU]) INNER JOIN [FG to CG mapping] ON [Finished Goods].[FGSKU] = [FG to CG mapping].[FGSKU]) ON [Component Goods].[CGSKU] = [FG to CG mapping].[CGSKU];

並添加了標準：

SELECT [FG Orders by Date].FGOrderID, 
      [FG Orders by Date].[FG Date], 
      [FG Orders by Date].[FG Date] AS ExpTodaysDate, 
      [FG Orders by Date].FGSKU, 
      [Finished Goods].[FG Name], 
      [FG to CG mapping].CGSKU, 
      [Component Goods].[CG Category], 
      [CG Category].[CG Category Name], 
      [Component Goods].[CG Name], 
      [Component Goods].[CG Unit], 
      Units.[Unit name], 
      [Component Goods].[CG Unit value], 
      [FG Orders by Date].[FGSKU Quantity], 
      [FG to CG mapping].[CG Qty Required for FGSKU], 
      [FGSKU Quantity]*[CG Qty Required for FGSKU] AS TotalQtyOfCGSKU
FROM (Units INNER JOIN ([CG Category] INNER JOIN [Component Goods] ON [CG Category].[CG Category ID] = [Component Goods].[CG Category]) ON Units.[Unit Symbol] = [Component Goods].[CG Unit]) INNER JOIN (([Finished Goods] INNER JOIN [FG Orders by Date] ON [Finished Goods].[FGSKU] = [FG Orders by Date].[FGSKU]) INNER JOIN [FG to CG mapping] ON [Finished Goods].[FGSKU] = [FG to CG mapping].[FGSKU]) ON [Component Goods].[CGSKU] = [FG to CG mapping].[CGSKU]
WHERE ((([FG Orders by Date].[FG Date])=Date()));

SQL 創建相關表的語句

（這是基於使用DBWScript的導出）

CREATE TABLE [CG Category] (
   [CG Category ID] AUTOINCREMENT,
   [CG Category Name] TEXT(255) WITH COMPRESSION,
   CONSTRAINT [ID] PRIMARY KEY ([CG Category ID])
);
ALTER TABLE [CG Category] ALLOW ZERO LENGTH [CG Category Name];
ALTER TABLE [CG Category] FORMAT [CG Category Name] SET "@";
CREATE TABLE [Component Goods] (
   [CGSKU] DOUBLE NOT NULL,
   [CG Name] TEXT(255) WITH COMPRESSION,
   [CG Unit] TEXT(255) WITH COMPRESSION NOT NULL,
   [CG Unit value] DOUBLE,
   [CG Category] LONG,
   [CG Pick Location] TEXT(255) WITH COMPRESSION,
   CONSTRAINT [CGSKU] PRIMARY KEY ([CGSKU])
);
ALTER TABLE [Component Goods] ALLOW ZERO LENGTH [CG Name];
ALTER TABLE [Component Goods] ALLOW ZERO LENGTH [CG Unit];
ALTER TABLE [Component Goods] ALLOW ZERO LENGTH [CG Pick Location];
ALTER TABLE [Component Goods] FORMAT [CGSKU] SET "General Number";
ALTER TABLE [Component Goods] FORMAT [CG Name] SET "@";
ALTER TABLE [Component Goods] FORMAT [CG Unit value] SET "General Number";
ALTER TABLE [Component Goods] FORMAT [CG Pick Location] SET "@";
CREATE TABLE [FG Category] (
   [CG Category ID] AUTOINCREMENT,
   [CG Category Name] TEXT(255) WITH COMPRESSION,
   CONSTRAINT [ID] PRIMARY KEY ([CG Category ID])
);
ALTER TABLE [FG Category] ALLOW ZERO LENGTH [CG Category Name];
ALTER TABLE [FG Category] FORMAT [CG Category Name] SET "@";
CREATE TABLE [FG Orders by Date] (
   [FGOrderID] AUTOINCREMENT,
   [FG Date] DATETIME DEFAULT Now(),
   [FGSKU] DOUBLE,
   [FGSKU Quantity] LONG DEFAULT 0,
   CONSTRAINT [PrimaryKey] PRIMARY KEY ([FGOrderID])
);
ALTER TABLE [FG Orders by Date] FORMAT [FG Date] SET "Short Date";
ALTER TABLE [FG Orders by Date] DECIMAL PLACES [FGSKU Quantity] SET 0;
ALTER TABLE [FG Orders by Date] FORMAT [FGSKU Quantity] SET "General Number";
CREATE TABLE [FG to CG mapping] (
   [FGCG ID] AUTOINCREMENT,
   [FGSKU] DOUBLE,
   [CGSKU] DOUBLE,
   [CG Qty Required for FGSKU] DOUBLE,
   CONSTRAINT [ID] PRIMARY KEY ([FGCG ID])
);
ALTER TABLE [FG to CG mapping] FORMAT [FGSKU] SET "General Number";
ALTER TABLE [FG to CG mapping] FORMAT [CGSKU] SET "General Number";
ALTER TABLE [FG to CG mapping] FORMAT [CG Qty Required for FGSKU] SET "General Number";
CREATE TABLE [Finished Goods] (
   [FGSKU] DOUBLE NOT NULL,
   [FG Name] TEXT(255) WITH COMPRESSION,
   [FG Category] LONG,
   [FG Category Old] TEXT(255) WITH COMPRESSION,
   [FG Pick Location] TEXT(255) WITH COMPRESSION,
   [FG Handle in System] BIT NOT NULL DEFAULT =Yes,
   CONSTRAINT [FGSKU] PRIMARY KEY ([FGSKU])
);
ALTER TABLE [Finished Goods] ALLOW ZERO LENGTH [FG Name];
ALTER TABLE [Finished Goods] ALLOW ZERO LENGTH [FG Category Old];
ALTER TABLE [Finished Goods] ALLOW ZERO LENGTH [FG Pick Location];
ALTER TABLE [Finished Goods] FORMAT [FGSKU] SET "General Number";
ALTER TABLE [Finished Goods] FORMAT [FG Name] SET "@";
ALTER TABLE [Finished Goods] FORMAT [FG Category Old] SET "@";
ALTER TABLE [Finished Goods] FORMAT [FG Pick Location] SET "@";
ALTER TABLE [Finished Goods] FORMAT [FG Handle in System] SET "Yes/No";
CREATE TABLE [Units] (
   [Unit ID] AUTOINCREMENT,
   [Unit name] TEXT(255) WITH COMPRESSION,
   [Unit Symbol] TEXT(255) WITH COMPRESSION,
   CONSTRAINT [PrimaryKey] PRIMARY KEY ([Unit ID])
);
ALTER TABLE [Units] ALLOW ZERO LENGTH [Unit name];
ALTER TABLE [Units] ALLOW ZERO LENGTH [Unit Symbol];
ALTER TABLE [Units] FORMAT [Unit name] SET "@";
ALTER TABLE [Units] FORMAT [Unit Symbol] SET "@";
CREATE INDEX [CG Category]
   ON [Component Goods] ([CG Category]);
CREATE INDEX [CG Unit]
   ON [Component Goods] ([CG Unit]);
CREATE INDEX [FGSKU]
   ON [FG Orders by Date] ([FGSKU]);
CREATE INDEX [FG to CG mappingCGSKU]
   ON [FG to CG mapping] ([CGSKU]);
CREATE INDEX [FG to CG mappingFGSKU]
   ON [FG to CG mapping] ([FGSKU]);
CREATE INDEX [FG Category]
   ON [Finished Goods] ([FG Category]);
CREATE INDEX [Unit ID]
   ON [Units] ([Unit ID]);
CREATE INDEX [Unit Symbol]
   ON [Units] ([Unit Symbol]);
ALTER TABLE [Component Goods]
   ADD CONSTRAINT [CG CategoryComponent Goods]
   FOREIGN KEY NO INDEX ([CG Category]) REFERENCES
       [CG Category] ([CG Category ID]);
ALTER TABLE [Component Goods]
   ADD CONSTRAINT [UnitsComponent Goods]
   FOREIGN KEY NO INDEX ([CG Unit]) REFERENCES
       [Units] ([Unit Symbol]);
ALTER TABLE [FG Orders by Date]
   ADD CONSTRAINT [Finished GoodsFG Orders by Date]
   FOREIGN KEY NO INDEX ([FGSKU]) REFERENCES
       [Finished Goods] ([FGSKU]);
ALTER TABLE [FG to CG mapping]
   ADD CONSTRAINT [Component GoodsFG to CG mapping]
   FOREIGN KEY NO INDEX ([CGSKU]) REFERENCES
       [Component Goods] ([CGSKU]);
ALTER TABLE [FG to CG mapping]
   ADD CONSTRAINT [Finished GoodsFG to CG mapping]
   FOREIGN KEY NO INDEX ([FGSKU]) REFERENCES
       [Finished Goods] ([FGSKU]);
ALTER TABLE [Finished Goods]
   ADD CONSTRAINT [FG CategoryFinished Goods]
   FOREIGN KEY NO INDEX ([FG Category]) REFERENCES
       [FG Category] ([CG Category ID]);

日期未正確排序的查詢範例

（抱歉，我嘗試在 Markdown 中發布此內容，但我無法弄清楚為什么生成的程式碼格式不正確）

顯示日期未正確排序的查詢

你錯過了時間部分

$$ FG Orders by Date $$.$$ FG Date $$因為它被定義為

[FG Date] DATETIME DEFAULT Now()

您的表格顯示格式化的數據（僅日期部分），

ALTER TABLE [FG Orders by Date] FORMAT [FG Date] SET "Short Date"

但在數據庫中它與時間部分一起儲存（打開表

$$ FG Orders by Date $$並點擊$$ FG Date $$欄位，它將顯示其真實值）。 不要隱藏資料結構（表級別的格式，多值欄位，..），格式可以在查詢中完成：

SELECT Format([FG Orders by Date].[FG Date],"Short Date") As [FG Date Formatted], ... .

日期時間儲存為雙精度值，其中分數是時間部分（一天中經過的時間/一天中的整個時間），但沒有時間的日期只是一個整數值例如：

CDbl(#2019-06-18 00:00:00#) = 43634 = CInt(#2019-06-18#)

（0 小時/24 小時，分數為 0）

和：

CDbl(#2019-06-18 12:00:00#) = 43634.5 <> CInt(#2019-06-18 12:00:00#) =  43634

（12 小時/24 小時，分數為 0.5）

如果你的

$$ FG Orders by Date $$.$$ FG Date $$與時間部分一起儲存（它具有Now()預設值，而不是Date()），它與您的條件不匹配，=Date()因為日期時間中日期的唯一匹配項是午夜日期時間。 一整天的標準 2019-06-18

([FG Orders by Date].[FG Date] >= #06/18/2019# AND [FG Orders by Date].[FG Date] < #06/19/2019#)

因為日期時間可能介於這一天和第二天之間。

今天日期的標準可以表示為：

([FG Orders by Date].[FG Date] >= Date() AND [FG Orders by Date].[FG Date] < DateAdd("d", 1, Date())

你可能想聰明點，只使用日期部分

$$ FG Date $$和：

DateValue([FG Orders by Date].[FG Date]) = Date()

這可以返回相同的結果，但您需要知道這會阻止在

$$ FG Orders by Date $$.$$ FG Date $$，是什麼減慢了查詢，如果$$ FG Orders by Date $$.$$ FG Date $$Is Null 這會引發DateValue()函式錯誤。

引用自：https://dba.stackexchange.com/questions/240501

相關問答

Query

3個簡單查詢SQL學生題，Microsoft Access中的查詢錯誤

  • March 15, 2021
檢視問答
Query

僅返回日期在最近的 9 月 1 日之後的記錄

  • February 27, 2021
檢視問答
Query

有重複日期時僅選擇 Max(date)

  • May 16, 2019
檢視問答
Ms-Access

如何在不創建循環引用的情況下在 Access 中查詢此數據

  • December 17, 2018
檢視問答
Postgresql

能夠根據事件查詢計算天數的 SQL 查詢

  • December 4, 2021
檢視問答
Query

從前一天到今天創建每日搜尋查詢

  • August 27, 2021
檢視問答