Setting Date in Stored Procedure

%3CLINGO-SUB%20id%3D%22lingo-sub-965483%22%20slang%3D%22en-US%22%3ESetting%20Date%20in%20Stored%20Procedure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-965483%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20a%20stored%20procedure%20that%20looks%20for%20orders%20within%20a%20date%20period.%20How%20do%20I%20set%20the%20date%20within%20the%20stored%20procedure%3F%20Here%20is%20my%20code.%20Thank%20you!!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EUSE%20%5BTMWSUITE%5D%3CBR%20%2F%3EGO%3C%2FP%3E%3CP%3ESET%20ANSI_NULLS%20ON%3CBR%20%2F%3EGO%3CBR%20%2F%3ESET%20QUOTED_IDENTIFIER%20ON%3CBR%20%2F%3EGO%3CBR%20%2F%3ECREATE%20Proc%20%5Bdbo%5D.%5BDawgAlert_Completed_Order_Status%5D%3CBR%20%2F%3EAS%3CBR%20%2F%3ESELECT%3CBR%20%2F%3Eorderheader.ord_number%3CBR%20%2F%3E%2Corderheader.ord_billto%3CBR%20%2F%3E%2Corderheader.ord_status%3CBR%20%2F%3E%2Corderheader.ord_origin_earliestdate%3CBR%20%2F%3E%2Corderheader.ord_origin_latestdate%3CBR%20%2F%3E%2Corderheader.ord_dest_earliestdate%3CBR%20%2F%3E%2Corderheader.ord_dest_latestdate%3CBR%20%2F%3E%2Corderheader.ord_trailer%3CBR%20%2F%3E%2Corderheader.ord_totalmiles%3CBR%20%2F%3E%2Cstops.stp_city%3CBR%20%2F%3E%2Cstops.stp_state%3CBR%20%2F%3E%2Cstops.stp_schdtearliest%3CBR%20%2F%3E%2Cstops.stp_schdtlatest%3CBR%20%2F%3E%2Cstops.stp_status%3CBR%20%2F%3E%2Cstops.stp_number%3CBR%20%2F%3E%2Cstops.stp_sequence%3CBR%20%2F%3E%2Cstops.stp_arrivaldate%3CBR%20%2F%3E%2Cstops.stp_departuredate%3CBR%20%2F%3E%2Cstops.cmd_code%3CBR%20%2F%3E%2Cstops.cmp_name%3CBR%20%2F%3E%2Corderheader.ord_refnum%3CBR%20%2F%3E%2Corderheader.ord_description%3CBR%20%2F%3E%2Ctrailerprofile.trl_number%3CBR%20%2F%3E%2Ctrailerprofile.trl_type3%3CBR%20%2F%3E%2Ccity.cty_nmstct%3CBR%20%2F%3E%2Corderheader.ord_completiondate%3CBR%20%2F%3EFROM%3CBR%20%2F%3Eorderheader%3CBR%20%2F%3EINNER%20JOIN%20stops%3CBR%20%2F%3EON%20orderheader.ord_hdrnumber%20%3D%20stops.ord_hdrnumber%3CBR%20%2F%3ELEFT%20OUTER%20JOIN%20company%3CBR%20%2F%3EON%20orderheader.ord_company%20%3D%20company.cmp_id%3CBR%20%2F%3EINNER%20JOIN%20trailerprofile%3CBR%20%2F%3EON%20orderheader.ord_trailer%20%3D%20trailerprofile.trl_number%3CBR%20%2F%3EINNER%20JOIN%20city%3CBR%20%2F%3EON%20stops.stp_city%20%3D%20city.cty_code%3CBR%20%2F%3EWHERE%3CBR%20%2F%3Estops.stp_departure_status%20%3D%20'DNE'%20AND%20orderheader.ord_status%3D'CMP'%20AND%20(orderheader.ord_completiondate%20%26gt%3B%3D%20%40LoadDate)%20AND%20(orderheader.ord_completiondate%20%26lt%3B%3D%20%40EndDate)%20AND%20(orderheader.ord_billto%20%3D%20%40billto)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-965483%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Edates%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EStored%20Procedure%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-983706%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20Date%20in%20Stored%20Procedure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-983706%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F238560%22%20target%3D%22_blank%22%3E%40Tim%20Hunter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDECLARE%26nbsp%3B%3CSPAN%3E%40LoadDate%20DATE%3D'2019-11-05'%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Regular Contributor

I have a stored procedure that looks for orders within a date period. How do I set the date within the stored procedure? Here is my code. Thank you!!

USE [TMWSUITE]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Proc [dbo].[DawgAlert_Completed_Order_Status]
AS
SELECT
orderheader.ord_number
,orderheader.ord_billto
,orderheader.ord_status
,orderheader.ord_origin_earliestdate
,orderheader.ord_origin_latestdate
,orderheader.ord_dest_earliestdate
,orderheader.ord_dest_latestdate
,orderheader.ord_trailer
,orderheader.ord_totalmiles
,stops.stp_city
,stops.stp_state
,stops.stp_schdtearliest
,stops.stp_schdtlatest
,stops.stp_status
,stops.stp_number
,stops.stp_sequence
,stops.stp_arrivaldate
,stops.stp_departuredate
,stops.cmd_code
,stops.cmp_name
,orderheader.ord_refnum
,orderheader.ord_description
,trailerprofile.trl_number
,trailerprofile.trl_type3
,city.cty_nmstct
,orderheader.ord_completiondate
FROM
orderheader
INNER JOIN stops
ON orderheader.ord_hdrnumber = stops.ord_hdrnumber
LEFT OUTER JOIN company
ON orderheader.ord_company = company.cmp_id
INNER JOIN trailerprofile
ON orderheader.ord_trailer = trailerprofile.trl_number
INNER JOIN city
ON stops.stp_city = city.cty_code
WHERE
stops.stp_departure_status = 'DNE' AND orderheader.ord_status='CMP' AND (orderheader.ord_completiondate >= @LoadDate) AND (orderheader.ord_completiondate <= @EndDate) AND (orderheader.ord_billto = @billto)

1 Reply
Highlighted

@Tim Hunter 

DECLARE @LoadDate DATE='2019-11-05'