1

Currently I am wanting to test my new code with declaring a variable to have a set value with regards to returning results based off the current date from the field "DateShipped". But from researching I have noticed SQL Server 2000 did not acquire "GETDATE()" until 2008 and so DATETIME is required. With each different conversion format I try it seems I can't get a set of results to return though there is current records available today. Any help would be appreciated.

DateTime Column Value: 2016-05-19 08:40:18.287

Code Sections having trouble with:

DECLARE @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

***Code Below:

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
Improve this question

1 Answer 1

Reset to default
1

Why don't you declare two variables (@Today and @Tomorrow) and select where date_shipped >= @Today and 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

So - after declaring your variables for @Today and @Tomorrow and setting their values accordingly, your WHERE clause would be something like this

WHERE O.date_shipped >= @Today and O.date_shipped < @Tomorrow

Assuming you want to put this logic in a stored procedure and have a particular DateTime value passed in (instead of always using GETDATE()), you could create something like this:

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
Improve this answer
4
  • What would the where clause look like?. Commented May 10, 2017 at 12:19
  • I've updated my answer. Commented May 10, 2017 at 12:22
  • Thanks, I appreciate your help. This worked fine for testing purposes. One question I had (and forgive me I don't have to much experience with variables just yet) But if i want to save this as a stored procedure and have a parameter in my report for the user to search by date I would simply declare my dateshipped variable as date time and set it as "SET date_shipped = CONVERT(varchar(10), date_shipped, 23)" then in the where clause say "WHERE O.date_shipped = dateshipped. And the user could execute with a given value correct? Commented May 10, 2017 at 12:31
  • I've updated my answer with some additional information. Commented May 10, 2017 at 12:49

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.