Variable Dates in Scheculed Jobs

Question

question

Cer asked Mar 12, '10

Variable Dates in Scheculed Jobs

I need to alter the following procedure so that it can be run as a job each month using the 23rd of the month to the last day of the month as the date parameters, can anyone help please?

@sIncludedList_in       NVARCHAR(200)   =N'13,177,178'
,@dMinRenewalDate_in    DATETIME        = NULL
,@dMaxRenewalDate_in    DATETIME        = NULL
WITH RECOMPILE
AS

SET DATEFORMAT ymd

DECLARE @tmpDate            DATETIME
DECLARE @dMaxRenewalDate    DATETIME
DECLARE @dMinRenewalDate    DATETIME
DECLARE @sSqlText           VARCHAR(7950)

IF @dMinRenewalDate_in IS NULL
    SET @dMinRenewalDate_in = GETDATE()

IF @dMaxRenewalDate_in IS NULL
    SET @dMaxRenewalDate_in = @dMinRenewalDate_in

SET @dMinRenewalDate = CAST(CONVERT(VARCHAR(10),@dMinRenewalDate_in,121) AS SMALLDATETIME)

SET @tmpDate = CAST(CONVERT(VARCHAR(10),DATEADD(day,1,@dMaxRenewalDate_in),121) AS DATETIME)
SET @dMaxRenewalDate = DATEADD(ss,-1,@tmpDate)

PRINT 'Statuses: ' + @sIncludedList_in
PRINT 'Min Date: ' + CONVERT(VARCHAR(30),@dMinRenewalDate,121)
PRINT 'Max Date: ' + CONVERT(VARCHAR(30),@dMaxRenewalDate,121)

-- Force DateFormat on Renewal Date
SET @dMinRenewalDate_in = CAST(CONVERT(VARCHAR(10),@dMinRenewalDate_in,121) AS SMALLDATETIME)
-- Force DateFormat on Max Renewal Date (to be the before midnight of the specified date)
SET @dMaxRenewalDate_in = CAST(CONVERT(VARCHAR(10),DATEADD(day,1,@dMaxRenewalDate_in),121) AS SMALLDATETIME)


SELECT   sle.SalesID
        ,sle.ParentSalesID
        ,sle.CustomerRef                        COLLATE SQL_Latin1_General_CP1_CI_AS    AS CustomerRef
        ,COALESCE (cuk.[Sell-to Customer Name],cnl.[Sell-to Customer Name] COLLATE Latin1_General_CI_AS) AS Company
        ,sle.SaleStatusID
        ,sts.DescriptionEN                      COLLATE SQL_Latin1_General_CP1_CI_AS    AS SaleStatusDesc
        ,sle.RecordedDateTime
        ,sle.StartDate
        ,sle.EndDate
        ,sle.SaleType
        ,lst.DescriptionEN                      COLLATE SQL_Latin1_General_CP1_CI_AS    AS SellTypeDesc
--      ,sle.TotalPrice
        ,CASE sle.DLCountryCode
            WHEN 'GB' THEN '£' + CAST(sle.TotalPrice AS VARCHAR)
                        ELSE CAST(sle.TotalPrice AS VARCHAR)+'€'
         END AS TotalPrice
        ,sle.AutoRenew
        ,sle.CompanyCountryCode                 COLLATE SQL_Latin1_General_CP1_CI_AS    AS CompanyCountryCode
        ,sle.DLCountryCode                      COLLATE SQL_Latin1_General_CP1_CI_AS    AS DLCountryCode
        ,sle.CancellationCode
FROM    dbo._Sales AS sle WITH (NOLOCK)
        INNER JOIN  dbo.CSVToTable(@sIncludedList_in) AS CSVToTable_1 ON sle.SaleStatusID = CSVToTable_1.ID
        LEFT OUTER JOIN dbo._LookUpData AS lst ON sle.SaleType = lst.ID AND lst.fkLookUpType = 2
        LEFT OUTER JOIN dbo._LookUpData AS sts ON sts.ID = sle.SaleStatusID AND sts.fkLookUpType = 1
        LEFT JOIN SAFES00.navdbase360sql.dbo.[Creditsafe***live***Company$Contract Head]    cuk ON cuk.[Organisationsnummer] = sle.CustomerRef
        --LEFT JOIN CSUKS00.navdbase360sql.dbo.[NL Creditsafe$Contract Head]                    cnl ON cnl.[Organisationsnummer] COLLATE Latin1_General_CI_AS = sle.CustomerRef
        LEFT JOIN SAFES00.navdbase360sql.dbo.[NL Creditsafe$Contract Head]                  cnl ON cnl.[Organisationsnummer] COLLATE Latin1_General_CI_AS = sle.CustomerRef
WHERE   (sle.StartDate < @dMinRenewalDate)
AND     (sle.EndDate BETWEEN @dMinRenewalDate AND @dMaxRenewalDate)
ORDER BY sle.EndDate
        ,sle.CustomerRef
        ,sle.SalesID

stored-procedures job date-functions

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-12T09:15:14Z

David Wimbush answered Mar 12, '10

If I understand correctly, your problem is how to work out the 23rd and last day of the current month. Try this:

declare @now datetime
    , @from datetime
    , @to datetime
    , @mm varchar(2)

set @now = getdate()

if month(@now) < 10
    set @mm = '0' + month(@now)
else
    set @mm = month(@now)

set @from = cast(cast(year(@now) as varchar(4)) + '-' + @mm + '-23' as datetime)

set @to = cast(cast(year(@now) as varchar(4)) + '-' + @mm + '-01' as datetime)
set @to = dateadd(month, 1, @to)
set @to = dateadd(day, -1, @to)

select @from
    , @to

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question