SQL Server 2000 Declaring DateTime Variable to retrieve results based on current date. But receiving no results based off conversion

Question

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 

Answer 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