Get Dates till the given dates?

Transact-SQL
1

CREATE PROCEDURE [dbo].[USR_MBR_RenewalStatistics_SP_Modified]
@dteRenewalDate AS DATETIME, @Invoicedate AS DATETIME

AS

BEGIN

DECLARE @dteRenewMonth AS DATETIME
DECLARE @dteRenewMonthPriorSegment1 AS DATETIME
DECLARE @dteRenewMonthPriorSegment2 AS DATETIME
DECLARE @intRenewWithinDays AS INTEGER

SET @dteRenewMonth = dbo.udfn_FirstOfMonth(@dteRenewalDate, 0)
SET @dteRenewMonthPriorSegment1 = DATEADD(YEAR, -1, @dteRenewMonth)
SET @dteRenewMonthPriorSegment2 = DATEADD(YEAR, -2, @dteRenewMonth)
SET @intRenewWithinDays = DATEDIFF(DAY, @dteRenewMonth, @dteRenewalDate)

SELECT
ORDER_DETAIL.SHIP_MASTER_CUSTOMER_ID AS MasterCustomerID,
ORDER_DETAIL.SHIP_SUB_CUSTOMER_ID AS SubCustomerID,
ORDER_DETAIL.ORDER_NO AS OriginalOrderNumber,
ORDER_DETAIL.ORDER_LINE_NO AS OriginalOrderLineNumber,
ORDER_DETAIL.ORDER_DATE AS OriginalOrderDate,
ORDER_DETAIL.INVOICE_DATE AS OrigialInvoiceDate,
ORDER_DETAIL.PRODUCT_CODE AS OriginalProductCode,
ORDER_DETAIL.LINE_STATUS_CODE AS OriginalLineStatusCode,
ORDER_DETAIL.FULFILL_STATUS_CODE AS OriginalFulfillStatusCode,
ORDER_DETAIL.CYCLE_BEGIN_DATE AS OriginalCycleBeginDate,
ORDER_DETAIL.CYCLE_END_DATE AS OriginalCycleEndDate,
ORDER_DETAIL.ACTUAL_TOTAL_AMOUNT AS OriginalPaid,
ORDER_DETAIL.MARKET_CODE AS OriginalMarketCode,
PRODUCT.SHORT_NAME AS OriginalProduct,
dbo.udfn_FirstOfMonth(CYCLE_END_DATE , 0) AS RenewDate,
RenewalInformation.Renewed,
RenewalInformation.Paid AS RenewalPaid,
RenewalInformation.MarketCode AS RenewalMarketCode,
RenewalInformation.OrderNumber AS RenewalOrderNumber,
RenewalInformation.OrderLineNumber AS RenewalOrderLineNumber
FROM
ORDER_DETAIL
INNER JOIN PRODUCT ON ORDER_DETAIL.PRODUCT_CODE = PRODUCT.PRODUCT_CODE
CROSS APPLY dbo.udtfn_RenewalWithinTimeframe(SHIP_MASTER_CUSTOMER_ID, SHIP_SUB_CUSTOMER_ID, ORDER_NO, dbo.udfn_FirstOfMonth(CYCLE_BEGIN_DATE, 0), @intRenewWithinDays) AS RenewalInformation
WHERE
(ORDER_DETAIL.PRODUCT_CODE LIKE 'SOC%')
AND (ORDER_DETAIL.SUBSYSTEM = 'MBR') AND(RATE_CODE = 'RENEW')
AND (dbo.udfn_FirstOfMonth(CYCLE_BEGIN_DATE, 0) IN (@dteRenewMonth, @dteRenewMonthPriorSegment1, @dteRenewMonthPriorSegment2 ) ) AND @Invoicedate = ORDER_DETAIL.INVOICE_DATE

END

EXECUTE [USR_MBR_RenewalStatistics_SP_Modified] '01/01/2017', '03/27/2014'

How do i get all dates till the date i provided in stored procedure parameter. like till 03/27/2014.

Whenever i provide invoicedate, I need all dates till that date ??

2

Can't you add a where clause:

AND order_detail.invoice_date <= @Invoicedate

1 Like
3

Beware that that format of string-date is ambiguous - depending on the language of the currently connected user, and a bunch of other things (which may very well change) then that date format will not be parsed as mm/dd/yyyy

Using 'yyyymmdd' (or ISO format 'yyyy-mm-ddThh:mm:ss') is unambiguous, otherwise use CONVERT [but that won't work with DATETIME parameters to your Sproc and Date Strings in the EXEC as the conversion is IMplicit)

1 Like