T-Sql
SQL Server 2000 聲明 DateTime 變數以根據目前日期檢索結果。但是沒有收到基於轉換的結果
目前,我想通過聲明一個變數來測試我的新程式碼,以根據欄位“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 >= @Today and O.date_shipped < @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