T-Sql

SQL Server 2000 聲明 DateTime 變數以根據目前日期檢索結果。但是沒有收到基於轉換的結果

  • May 10, 2017

目前，我想通過聲明一個變數來測試我的新程式碼，以根據欄位“DateShipped”的目前日期返回結果。但是通過研究我注意到 SQL Server 2000 直到 2008 年才獲得“GETDATE()”，因此需要 DATETIME。對於我嘗試的每種不同的轉換格式，儘管今天有可用的目前記錄，但我似乎無法返回一組結果。任何幫助，將不勝感激。

日期時間列值：2016-05-19 08:40:18.287

程式碼部分有問題：

聲明@date_shipped DATETIME

SET @date_shipped = dateadd(dd,datediff(dd,0,getdate()),0)

WHERE  DATEADD(dd, 0, DATEDIFF(dd, 0, O.date_shipped)) = @date_shipped

***程式碼如下：

DECLARE @date_shipped DATETIME 
       SET @date_shipped = dateadd(dd,datediff(dd,0,getdate()),0)
--@location VARCHAR(10)
--SET @location = 'RAS 2'
--SET @date_shipped = CONVERT(varchar(10), @date_shipped, 23)




SELECT  
     O.Date_Shipped
   , L.Shipped  
   , L.Price 
    , O.Order_no
   , O.Ext
   , O.Cust_Code 
   , O.Ship_To_Name 
   , O.Freight_Allow_Type 
   , TCT.Carton_no 
    , TPM.dim_ext_x  
    , TPM.dim_ext_y 
   , TRCB.Number_of_Boxes 
    , TRCB.Skid_Height_Inches 
   , MAX(O.Routing) AS 'Routing'  
   , MAX(O.User_def_fld9) AS 'bol_no'  
   , MAX(Ship_to_name) AS Ship_to_name_1 
   , MAX(Ship_to_add_3) AS Ship_to_add_3 
   , MAX(L.[location]) AS 'Location' 
   , MAX(O.cust_code ) AS 'Cust_code_1' 
   --, CONVERT(DATETIME, CONVERT(varchar(20) , MAX(O.date_shipped), 23)) AS 'date_shipped'
   , MAX(O.user_def_fld10) AS 'Skids' 
   , MAX(O.user_def_fld12) AS 'Weight' 
   , ISNULL(MAX(BH.est_freight_cost),0) AS 'BOL_est_Freight'
    , TCT.[weight] + TPM.[weight] AS 'Total_Pallet_Weight'

---->>>>>Formula "CUBIC FEET"
   ,CASE 
       WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0
           THEN CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728) 
                ELSE 0 END AS 'Cubic_Feet' 
---->>>>>Formula "CUBIC FEET"


---->>>>>Formula "PCF"
   , CASE 
       WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0 
           THEN CONVERT(DECIMAL(10,4), (TCT.weight + TPM.weight) / ((TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))
               ELSE 0 END AS 'PCF'
---->>>>>Formula "PCF"


---->>>>>Formula "TOTAL_PCF"
   , CASE WHEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) > 0
        THEN
           CASE WHEN (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))) > 0 
                THEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) + (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728)))
                ELSE 0
           END
        ELSE 0  
     END AS 'Total_PCG'
---->>>>>Formula "TOTAL_PCF"


---->>>>>Formula "SHIP TOTAL"
   , CASE 
        WHEN (L.shipped * L.Price > 0 ) 
           THEN (L.Shipped * L.Price)
               ELSE 0 
                   END AS 'ShipTotal'
---->>>>>Formula "SHIP TOTAL"


------>>>>>>>>TEMP TABLE
--INTO #tempTruckSummary
------>>>>>>>>TEMP TABLE

FROM xfers X WITH (NOLOCK)

INNER JOIN xfer_list XL
   ON  X.xfer_no = XL.xfer_no
INNER JOIN orders O WITH (NOLOCK)
   ON X.xfer_no = O.order_no
INNER JOIN ord_list L
   ON  O.order_no = L.order_no
      AND O.order_no = L.order_no 
      AND O.ext = L.order_ext
      AND X.xfer_no = XL.xfer_no
INNER JOIN arcust C WITH (NOLOCK)
   ON O.cust_code = C.customer_code 
LEFT OUTER JOIN armaster A WITH (NOLOCK)
   ON A.customer_code = O.cust_code  
      AND A.ship_to_code = O.ship_to
      AND A.location_code = O.location  
INNER JOIN tdc_carton_tx TCT WITH (NOLOCK)
    ON O.order_no = TCT.order_no
LEFT OUTER JOIN tdc_revshelf_carton_box TRCB WITH (NOLOCK)
   ON TCT.order_no=TRCB.order_no 
      AND TCT.order_ext=TRCB.order_ext 
      AND TCT.carton_no=TRCB.carton_no   
LEFT OUTER JOIN tdc_pkg_master TPM WITH (NOLOCK)
    ON TCT.carton_type=TPM.pkg_code
JOIN ras_bol_details BD WITH (NOLOCK)
   ON X.xfer_no = BD.bl_src_no
      AND X.xfer_no = BD.bl_src_no AND BD.order_type IN ('T','S')
JOIN ras_bol_header BH WITH (NOLOCK)
   ON BD.bl_no = BH.bl_no
      AND O.order_no = BD.bl_src_no 
      AND O.ext = BD.bl_src_ext 


WHERE  DATEADD(dd, 0, DATEDIFF(dd, 0, O.date_shipped)) = @date_shipped
      AND X.to_loc IN ('KM', 'AWNC', 'KMUT', 'AWAZ', 'SM')
      AND O.routing NOT LIKE 'FEDX%'
      AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3')
      AND ISNULL(O.void,'') <> 'V'
    --  AND L.Location = @Location


GROUP BY
     O.date_shipped
   , L.shipped  
   , L.Price 
    , O.order_no
   , O.ext
   , O.cust_code 
   , O.ship_to_name 
   , O.freight_allow_type 
   , TCT.carton_no 
   , TCT.[weight]
   , TPM.[weight]
    , TPM.dim_ext_x  
    , TPM.dim_ext_y 
   , TRCB.number_of_boxes 
    , TRCB.skid_height_inches

為什麼不聲明兩個變數（@Today 和 @Tomorrow）並選擇 date_shipped >= @Today 和 date_shipped < @Tomorrow

DECLARE @Today DATETIME
DECLARE @Tomorrow DATETIME

SET @Today = dateadd(dd, datediff(dd, 0, getdate()), 0)
SET @Tomorrow = dateadd(dd, datediff(dd, 0, getdate()) +1, 0)

PRINT @Today
PRINT @Tomorrow

所以 - 在為 @Today 和 @Tomorrow 聲明變數並相應地設置它們的值之後，你的WHERE子句將是這樣的

WHERE  O.date_shipped &gt;= @Today and O.date_shipped &lt; @Tomorrow

假設您想將此邏輯放在儲存過程中並傳入特定的 DateTime 值（而不是始終使用 GETDATE()），您可以創建如下內容：

CREATE PROCEDURE GetShippingInformation (@DateShipped DATETIME)
AS
BEGIN
   DECLARE @DateShippedBegin DATETIME
   DECLARE @DateShippedEnd DATETIME

   SET @DateShippedBegin = dateadd(dd, datediff(dd, 0, @DateShipped), 0)
   SET @DateShippedEnd = dateadd(dd, datediff(dd, 0, @DateShipped) + 1, 0)

   PRINT 'You are searching between ' + convert(varchar(20),@DateShippedBegin) + ' and ' + convert(varchar(20),@DateShippedEnd)
END
go
exec GetShippingInformation '2017-01-01'
GO
exec GetShippingInformation '2017-06-01'
GO

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

相關問答

Sql-Server

如何編寫選擇語句，根據partid 從最後一個日期到目前日期獲取數據並填補空白？

  • March 30, 2022
檢視問答
Sql-Server

即使使用非聚集索引和全文索引，SE LECT 查詢也很慢

  • February 21, 2022
檢視問答
T-Sql

獲取儲存過程的列依賴項的完整列表的方法？

  • January 5, 2022
檢視問答
Sql-Server

儲存過程中的局部變數

  • December 9, 2021
檢視問答
T-Sql

CPU vs Elapsed & Parallelism

  • November 12, 2021
檢視問答
Sql-Server

建議創建表或設計儲存過程以刪除 while 循環

  • October 21, 2021
檢視問答