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 
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
  • What would the where clause look like?. – tfenwick11 May 10 '17 at 12:19
  • I've updated my answer. – Scott Hodgin May 10 '17 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? – tfenwick11 May 10 '17 at 12:31
  • I've updated my answer with some additional information. – Scott Hodgin May 10 '17 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.