SQL Server Performance

Date Handling

Discussion in 'Getting Started' started by kalyformin, Oct 11, 2007.

  1. kalyformin New Member

    I'm quite new to SQL Server and I have a pretty naive question.
    I have a table called Company that has a field called Renewal date. I have a task that needs to be run on the first of every month to gather all companies that have Renewal Dates coming up in the next 180 days. The Renewal Date is a datetime field in Sql Server. Is there a way I can have this accomplished.

    One thing that still ponders my mind is how to handle potential "edge conditions" of 30 / 31 day months?
    February? etc. ANy help with the design/query is much appreciated. Thanks

  2. ndinakar Member

    I am not sure if you can directly schedule a job to run only on 1st of a month but you can schedule a job to run every day and check if its the 1st of the month usingf datepart, and if it is then query to get companies with renewal dates in next 180 days. I would start with writing the query first, assuming the day is already 1st and having gotten the query right, its pretty easy to schedule it as a job and exeute the query only if datepart(day) = 1.
  3. Greg Larsen New Member

    Here is a script that will create a schedule on a SQL Agent job named "MyJob" and will run it on the first day of every month.
    EXEC
    msdb.dbo.sp_add_jobschedule @job_name=N'MyJob', @name=N'Monthly on First Day of Every Month', @enabled=1, @freq_type
    =16, @freq_interval=1, @freq_subday_type
    =1, @freq_subday_interval=0, @freq_relative_interval
    =0, @freq_recurrence_factor=1, @active_start_date
    =20071011, @active_end_date=99991231, @active_start_time
    =0, @active_end_time
    =235959
  4. satya Moderator

    For a better optimization follow what Greg referred by keeping the process in a Stored procedure with an occasional update to exection plan.

Share This Page